Basic update command syntax incorrect

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

General SQL Server Forums

New to SQL Server Programming

Basic update command syntax incorrect

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-06-10 : 10:12:17

I'm embarrassed about this but can't get the syntax right on this update command. The first line is wrong but why?

update luoption o, luoptionoverride v
set o.sname = v.sname, o.dtmapplied = getdate()
from luoption o, luoptionoverride v
where o.loptionid = v.loptionid

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-06-10 : 10:33:50

you cannot update 2 tables at the same time.. you can either update luoption or luoptionoverride (right after the UPDATE command).
Also, start using ANSI standard joins such as INNER JOIN.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-06-10 : 11:03:09

quote:
Originally posted by dinakar

you cannot update 2 tables at the same time.. you can either update luoption or luoptionoverride (right after the UPDATE command).
Also, start using ANSI standard joins such as INNER JOIN.

Thanks for that info. So I have to use 2 update commands.
I tried using the standards but getting stuck. There is a 1 to 1 relationship between the 2 tables. i just need to override the sname in luoption with sname from luoptionoverride. I wanted to update the date field in the luoptionOverride table at the same time but guess I have to do this first then do the copy. What changes should i make to the following code?

update o
set o.sname = v.sname
from luoption o
inner join
luoptionoverride v on v.loptionid = o.loptionid

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-10 : 13:19:41

update o
set o.sname = v.sname,
o.dtmapplied = getdate()
from luoption o
inner join
luoptionoverride v on v.loptionid = o.loptionid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 13:26:46

quote:
Originally posted by raky

update o
set o.sname = v.sname,
o.dtmapplied = getdate()
from luoption o
inner join
luoptionoverride v on v.loptionid = o.loptionid

OP wants to update dtmapplied in table luoptionoverride.

No, you're never too old to Yak'n'Roll if you're too young to die.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-10 : 13:44:17

quote:
Originally posted by webfred

quote:
Originally posted by raky

update o
set o.sname = v.sname,
o.dtmapplied = getdate()
from luoption o
inner join
luoptionoverride v on v.loptionid = o.loptionid

OP wants to update dtmapplied in table luoptionoverride.

No, you're never too old to Yak'n'Roll if you're too young to die.

oh i missed the point...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 13:48:31

quote:
Originally posted by insanepaul

I'm embarrassed about this but can't get the syntax right on this update command. The first line is wrong but why?

update o
set o.sname = v.sname,
o.dtmapplied = getdate()
from luoption o
join luoptionoverride v
on o.loptionid = v.loptionid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 14:56:25

quote:
Originally posted by visakh16

quote:
Originally posted by insanepaul

I'm embarrassed about this but can't get the syntax right on this update command. The first line is wrong but why?

update o
set o.sname = v.sname,
o.dtmapplied = getdate()
from luoption o
join luoptionoverride v
on o.loptionid = v.loptionid

OP wants to update dtmapplied in table luoptionoverride.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources