update mutliple tables at same time

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)

update mutliple tables at same time

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-09-09 : 08:42:42

Hello,
hope you can help with this one :
I've got 3 tables, address_detail, address_head and address_link, detail links to head and head links to link, I want to import data from a spreadsheet into fields in those tables where the ID of address_detail = the ID in the spreadsheet.
I was thinking about doing something like :

update address_detail, address_head, address_link
SET address_detail.address1=spreadsheet.address1,
address_detail.address2=spreadsheet.address2,
address_head.name = spreaddsheet.name,
address_link.enddate = spreaddsheet.enddate
FROM speadsheet
WHERE address_detail.ID = address_head.d_ID
AND address_head.id=address_link.h_ID
AND address_detail.ID=speardsheet.id

or is this just nonsense ?

thanks for the help
Jamie

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-09 : 08:46:38

yup...nonsense.

an update statement can only update i table at a time.
it can however reference other tables while doing the update.

best bet....create a transaction...and wrap it around 3 update statements.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-09-09 : 08:54:20

do you mean

begin transaction

update address_d SET address_d.values FROM spreadsheet where address_d.ID=spreadsheet.id

update address_h, address_d SET address_h.values FROM spreadsheet where address_h.d_ID=address_d.ID AND address_d.ID=spreadsheet.ID

update address_l, address _h,address_d SET address_l.values FROM spreadsheet where address_h.d_ID=address_d.ID AND address_h.L_ID=address_L.ID AND address_d.ID=spreadsheet.ID

Commit

?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-09 : 09:02:33

nearly...

begin transaction
update address_d
from address_d SET address_d.values FROM spreadsheet where address_d.ID=spreadsheet.id

update address_h
from address_h
inner join address_d on address_h.d_ID=address_d.ID
SET address_h.values FROM spreadsheet
where address_d.ID=spreadsheet.ID

update address_l
from address_l
inner join address_h on address_h.L_ID=address_L.ID
inner join address_d on address_h.d_ID=address_d.ID
SET address_l.values FROM spreadsheet
where address_d.ID=spreadsheet.ID

Commit transaction

going this route...means all 3 update statements have to work...or none of them get committed....at least you can't get partial updates...ie 1 table updated and 2 not.

also best to try to use this style of update + (delete,select) statements...
it'll get you into using a consistant style of command....as the select/delete/update bits are interchangable with very little modification.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-09-09 : 09:08:20

I see what you mean, its also much easier to read.
thanks I'll try to write most of my statments using joins
als othe from and set was the wrong way round.
cheers for the help. !

Kristen
Test

22859 Posts

Posted - 2004-09-09 : 23:06:39

If you want to have it in one UPDATE statement (e.g. because Muppets need to be able to create the UPDATE statements) you could create a VIEW across the three tables and have an INSTEAD OF TRIGGER on the VIEW that caught the update and did the 3-table-update-in-a-transaction thingie.

Its probably a crazy idea, but just thought I'd mention it ...

Kristen

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-10 : 02:54:27

Kristen, you may feel in crazy. but good for me to know there is a way to update multiple table.

mk_garg

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-09-10 : 04:56:58

Kristen is instead of only availbe in sql 2000 ?
I am using SQL 7

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources