Update fields of different tables in a join

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

Update fields of different tables in a join

Author

Topic

lentissimo
Starting Member

4 Posts

Posted - 2010-03-19 : 07:37:08

In ms access is possible to update 2 (or more) fields of 2 (or more) tables in a join.
Is it possible in SQL Server?
I don't find how to do it.
Thanks.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-19 : 07:39:38

quote:
Originally posted by lentissimo

In ms access is possible to update 2 (or more) fields of 2 (or more) tables in a join.
Is it possible in SQL Server?
I don't find how to do it.
Thanks.

Yup.

UPDATE T1
set T1.COL1='VALUE'
FROM TABLE1 AS T1 INNER JOIN
TABLE2 AS T2 ON T1.ID=T2.ID

lentissimo
Starting Member

4 Posts

Posted - 2010-03-19 : 09:03:12

I had to change a field also in T2.
In ms access it's possible with 1 query.

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 09:18:41

I would use two UPDATE statements in a Transaction Block (so they either both happen, or neither does).


BEGIN TRANSACTION

UPDATE U
SET Col1 = 'VALUE'
FROM Table1 AS U
WHERE SomeCol = '123'

UPDATE U
SET Col2 = 'VALUE'
FROM Table1 AS T1
    JOIN Table2 AS U
        ON T1.ID = U.ID
WHERE T1.SomeCol = '123'

COMMIT

Might be possible to update two tables through a VIEW Joining them together?

You could probably do in one statement using the OUTPUT clause (can't remember when that came in, might have been SQL2008)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources