simple update question - 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 2000 Forums

Transact-SQL (2000)

simple update question

Author

Topic

cmadison2426
Starting Member

10 Posts

Posted - 2006-12-05 : 13:38:23

This might be a stupid question but why does this work:
UPDATE Table1
Set Table1.ColA = Table2.ColA
FROM Table2
WHERE Table1.ID = Table2.ID

and this doesn't:
UPDATE Table1
Set Table1.ColA = Table2.ColA
Set Table1.ColB = Table2.ColB
FROM Table2
WHERE Table1.ID = Table2.ID

The error I get is invalid syntax near Table1.ColB

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 14:21:39

You must only use the SET keyword once like this

UPDATE Table1
Set Table1.ColA = Table2.ColA,
   Table1.ColB = Table2.ColB
FROM Table2
WHERE Table1.ID = Table2.ID

cmadison2426
Starting Member

10 Posts

Posted - 2006-12-05 : 15:06:48

Thank you snSQL, you've helped me before. I looked through the help files but could only find examples of one column being updated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:08:43

Also there is no comma between the columns to UPDATE.

Peter Larsson
Helsingborg, Sweden

cmadison2426
Starting Member

10 Posts

Posted - 2006-12-06 : 09:32:11

Thanks Peso. Actually I had to put a comma between the columns or I would get an invalid syntax error. Is that strange?
Ex.
SET Table1.ColA = Table2.ColA,
Table1.ColB = Table2.ColB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 09:40:02

No. This is by design in T-SQL.

Peter Larsson
Helsingborg, Sweden

cmadison2426
Starting Member

10 Posts

Posted - 2006-12-06 : 10:06:44

Thanks snSQL and Peso for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources