UPDATE problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

UPDATE problem

Author

Topic

nemsho
Starting Member

4 Posts

Posted - 2011-12-03 : 04:35:25

Dear members of forum,

I'm novice is T-SQL and I have problems with column update.

Those are columns of my table _query1SP2009_10
TABLE [dbo].[_query1SP2009_10](
[SifraSmera] [char](2) NULL, --as DEPARTMENT OF FACULTY
[NazivSmera] AS NAMES' [varchar](50) NULL, - as Name of Department
[SifPredmeta] [char](6) NULL, as foreign key of table Courses
[NazivPredmeta] [varchar](50) NULL, as name of table Courses
[BrCasovaPred] [int] NULL, as professors' class
[BrCasovaVezbi] [int] NULL, as assistants' class
[casovaDrugo] [int] NULL, as class
[ESPB] [money] NULL,
[Obavezan] [int] NULL, as Elective courses in college
[Izborni] [int] NULL, as Compulsory courses in college
[Semestar] [int] NULL, as semestar
[godinaSlusanjaPredmeta] [int] NULL,
[studijskiProgram] [nchar](10) NULL,
[BrKolokvijuma] [int] NULL,
[BrSeminarskihRadova] [int] NULL,
[Modul] [char](2) NULL,
[NacinPolaganja] [char](2) NULL, as methods EXAMINATION
[idJezika] [int] NULL, foreign key LANGUAGES table
[idTipa] [int] NULL
) ON [PRIMARY]

The column sifPredmeta has repeating values, and each sifPredmeta has the same name. Problems are next. For some sifPredmeta, value columns nazivPredmeta is null. Therefore, for all same values in column sifPredmeta should have to same name in column nazivPredmeta. For some value sifPredmeta in my table _query1SP2009 column NazivPredmeta has null values , while in other recod for same sifPredmeta values of column nazivPredmeta is not null. I must need to update columns nazivPredmeta which has null.

*sifPredmeta *|*nazivPredmeta*
*******************************
* L0255 ******|* Informatika**
* L0255 ******|* NULL ********
* N5555 ******|* NULL ********
* N5555 ******|* Helthcare****
....
*******************************

How can I update column naZivPredmeta with 3-4 lines code.

I was tried with CASE END, but no results.

My code wrong cod:
******************************************************************
UPDATE _query1SP2009_10
set NazivPredmeta= case
WHEN sifPredmeta=sifpredmeta THEN NazivPredmeta
end
WHERE NazivPredmeta is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 04:38:54

[code]
UPDATE t
SET t.nazivPredmeta = t1.nazivPredmeta
FROM _query1SP2009_10 t
INNER JOIN (SELECT sifPredmeta,MAX(nazivPredmeta) AS nazivPredmeta
FROM _query1SP2009_10
GROUP BY sifPredmeta
)t1
ON t1.sifPredmeta = t.sifPredmeta
WHERE t.nazivPredmeta IS NULL
AND t1.nazivPredmeta IS NOT NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nemsho
Starting Member

4 Posts

Posted - 2011-12-03 : 05:36:15

quote:
Originally posted by visakh16

UPDATE t
SET t.nazivPredmeta = t1.nazivPredmeta
FROM _query1SP2009_10 t
INNER JOIN (SELECT sifPredmeta,MAX(nazivPredmeta) AS nazivPredmeta
FROM _query1SP2009_10
GROUP BY sifPredmeta
)t1
ON t1.sifPredmeta = t.sifPredmeta
WHERE t.nazivPredmeta IS NULL
AND t1.nazivPredmeta IS NOT NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16, Thank you, very very thak you.

You saved me time.

nemsho
Starting Member

4 Posts

Posted - 2011-12-03 : 05:38:03

visakh16, Thank you, very very thak you.

You saved me time!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 07:29:19

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources