Multiple Update Join - 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

General SQL Server Forums

New to SQL Server Programming

Multiple Update Join

Author

Topic

webappguru
Starting Member

10 Posts

Posted - 2010-06-04 : 11:56:11

Ok here is the situation:

I know that I can do an update with a join together. It does work however if I want to update 2 different values

So say we have table A and Table B

Update A
	   set A.Field = 'XYZ'
	FROM B
	  JOIN A on
	A.SomeField = B.SomeField
	WHERE SomeCriteria

This works for me... when I have 1 item to set, but I have to set 1 Item in 1 and 1 Item in the joined table

Update A
	   set A.Field = 'XYZ',
           B.SomeField = '1234'
	FROM B
	  JOIN A on
	A.SomeField = B.SomeField
	WHERE SomeCriteria

The above doesnt work saying that b.Somefield does not exist.

Any hints???

"I live for programming"
www.aliabidhusain.net

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-06-04 : 12:19:45

can't be done as designed - updates can only be done to 1 table at a time. however...

begin transaction
update A
set a.x = y
update B
set b.z = t
commit transaction

may deliver something suit your business needs

webappguru
Starting Member

10 Posts

Posted - 2010-06-04 : 12:58:18

Andrew,

Here is the deal... I need to merge one table's data with another. When I do the update to one table I want to set a flag on the other table to show that it got updated.

I guess this is a problem with the Update/Join

Thanks,

Ali

"I live for programming"
www.aliabidhusain.net

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-04 : 13:34:54

One way to do this is to create a Trigger on your TableA to check for updates. Within the trigger you can update Table B.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources