Update using 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update using Join

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2009-07-09 : 10:51:21

This is probably absurdly simple, but how do I change this select statement:

select * from client
full outer join clientAccount ca on ca.clientID = c.clientID
where c.accountManagerID in (53)
and ca.status = 2 and ca.siteID = 2

Into one that will update a field in all the records that the select query pulls back? I can't seem to find the correct syntax ...

Cheers,
Matt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-09 : 10:55:14

you want to update which table ? Which columns ? With What Value ?


select * from client c
full outer INNER join clientAccount ca on ca.clientID = c.clientID
where c.accountManagerID in (53)
and ca.status = 2 and ca.siteID = 2

accountManagerID is from which table ? the alias c is for table client ?

your full outer join here is in fact an INNER JOIN

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:57:50

quote:
Originally posted by mattt

This is probably absurdly simple, but how do I change this select statement:

select * from client
full outer join clientAccount ca on ca.clientID = c.clientID
where c.accountManagerID in (53)
and ca.status = 2 and ca.siteID = 2

Into one that will update a field in all the records that the select query pulls back? I can't seem to find the correct syntax ...

Cheers,
Matt

whats the purpose of full outer join? which table are you trying to update from which table?

mattt
Posting Yak Master

194 Posts

Posted - 2009-07-09 : 10:58:25

Thanks. I'm attempting to set client.accountManagerID = 91 for all those records.

And yes, the query is a mess. Apologies for that, I paraphrased it in a hurry, which is terribly bad form when asking for help :(

mattt
Posting Yak Master

194 Posts

Posted - 2009-07-09 : 11:00:36

quote:
Originally posted by visakh16

quote:
Originally posted by mattt

This is probably absurdly simple, but how do I change this select statement:

select * from client
full outer join clientAccount ca on ca.clientID = c.clientID
where c.accountManagerID in (53)
and ca.status = 2 and ca.siteID = 2

Into one that will update a field in all the records that the select query pulls back? I can't seem to find the correct syntax ...

Cheers,
Matt

whats the purpose of full outer join? which table are you trying to update from which table?

The full join should be an inner join.

I'm attempting to update a field in the client table. However, the records that need updating are selected on the condition of a field in the clientAccount table having a certain value.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-09 : 11:00:46

[code]
select *
update c
set accountManagerID = 91
from client c
INNER join clientAccount ca on ca.clientID = c.clientID
where c.accountManagerID in (53)
and ca.status = 2 and ca.siteID = 2
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 11:01:17

[code]
update c
set c.accountManagerID = 91
from client c
inner join clientAccount ca
on ca.clientID = c.clientID
where c.accountManagerID in (53)
and ca.status = 2 and ca.siteID = 2
[/code]

mattt
Posting Yak Master

194 Posts

Posted - 2009-07-09 : 11:03:05

Thanks to both of you. It was the alias I was messing up. Much appreciated.

Cheers,
Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources