update multiple tables? - 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

SQL Server Development (2000)

update multiple tables?

Author

Topic

codism
Starting Member

11 Posts

Posted - 2007-03-06 : 11:16:40

Can anyone give me a working example for updating multiple tables in one UPDATE statement or show me how it is working in the following script:

quote:

declare @a table (idx int, value int)
declare @b table (idx int, value int)

insert into @a values (1, 2)
insert into @a values (2, 3)

insert into @b values (1, 20)
insert into @b values (2, 30)

-- move values from table @b into table @a
-- but I got syntax error
update @a, @b
set @a.value = @b.value
from @a join @b on @a.idx = @b.idx

select * from @a

Some one suggested to write my update in this way:

quote:

update @a
set value=(select value from @b where @a.idx=@b.idx)
where exists (select value from @b where @a.idx=@b.idx)

However, the above script is considered low efficient especially when I have a lot of record to process. (for some special reason, I cannot create index on @b.idx)

Some posts suggest that updating multiple tables is impossible in sql server. Is that true?

Thanks in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 11:28:22

Updating multiple tables in single UPDATE statement is not possible and neither your example does so.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources