Update two tables using single update statement in SQL 2000

Question

Update two tables using single update statement in SQL 2000

iam777here4u-979986

SSC Rookie

Points: 31
More actions
January 5, 2009 at 4:43 am

#199570

Hi All,
Can anyone help be to find possibilities of updating two tables using single UPDATE statement in SQL 2000?
Is it possible?
Best Regards
Boney

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

not possible with a single sql statement.... each table updated gets its own UPDATE statement, but it's not hard to put them together as a transaction. both tables can be updated from some table or the results of a join of other tables.

In the future, try to provide more details...what you tried so far, table structure, sample data.

with no table structure provided and no definitions, here's a crappy example to get the thoughts flowing:

[font="Courier New"]

SET XACT_ABORT ON

BEGIN TRAN

--first table

UPDATE TABLE1

SET TABLE1.SOMEVALUE = THIRDTABLE.SOMEVALUE

FROM THIRDTABLE

WHERE TABLE1.PK = THIRDTABLE.PK

--second table

UPDATE TABLE2

SET TABLE2.SOMEVALUE = THIRDTABLE.SOMEVALUE,

AUDITDT=GETDATE()

FROM THIRDTABLE

WHERE TABLE2.PK = THIRDTABLE.PK

COMMIT TRAN

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

Why do you need it to be in a single statement?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning