updateing data in multiple table from one update

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 2005 Forums

Transact-SQL (2005)

updateing data in multiple table from one update

Author

Topic

orlando876
Starting Member

15 Posts

Posted - 2010-08-05 : 20:21:22

i would like to update at least three tables from one update statement.

i create a select statement showed the data i would like to update but i am unable to convert it to update statement

SELECT p1.r,p1.DR,h1.sb,h1.sbd FROM p1 inner join h1 on p1.[hid] = h1.[hid] inner join [RD] on RD.[hid] = hi.[hid] where h1.t = @T

i need to create an update statement that update those parm from the select statement

update (SELECT p1.r,p1.DR,h1.sb,h1.sbd FROM p1 inner join h1 on p1.[hid] = h1.[hid] inner join [RD] on RD.[hid] = hi.[hid])
set p1.r = @r, p1.dr = @dr..
where h1.t = @T

sql2008

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 00:57:32

Can you post some sample data?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

orlando876
Starting Member

15 Posts

Posted - 2010-08-08 : 19:19:47

can an update statement update more than one table at a time

orlando876
Starting Member

15 Posts

Posted - 2010-08-08 : 19:21:31

i have done it in microsoft access but it dont seem to work in sql sever any help i just need to know so i dont run three sql to do what one could do

orlando876
Starting Member

15 Posts

Posted - 2010-08-08 : 19:27:40

would an inner join work
update p1 inner join h1 on p1.[hid] = h1.[hid] inner join [RD] on RD.[hid] = hi.[hid])
set p1.r = @r, p1.dr = @dr
where h1.t = @T

this would work in access put get an
"Incorrect syntax near the keyword 'INNER'."

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-08 : 21:15:08

it is as easy as access, just different syntax.

update temp
set --- your modification
from
(
select column needed update from yourTable1
join yourTable2 on --condition1
join yourTable3 on --condition2
where -- condition3 if needed
)temp

NOTE: we can update columns from any, but only one, based table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-08 : 21:21:43

quote:
Originally posted by orlando876

can an update statement update more than one table at a time

No. An UPDATE statement only update ONE table

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-08 : 21:24:04

quote:
Originally posted by orlando876

would an inner join work
update p1 inner join h1 on p1.[hid] = h1.[hid] inner join [RD] on RD.[hid] = hi.[hid])
set p1.r = @r, p1.dr = @dr
where h1.t = @T

this would work in access put get an
"Incorrect syntax near the keyword 'INNER'."


UPDATE p1
SET    p1.r  = @r, 
       p1.dr = @dr
FROM   p1 
       inner join h1   on p1.[hid] = h1.[hid] 
       inner join [RD] on RD.[hid] = hi.[hid]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources