Update Statement Question - 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 2008 Forums

Transact-SQL (2008)

Update Statement Question

Author

Topic

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2011-10-16 : 10:09:33

Table A looks like

CustomerName,CustID, CustKey
Joe, 1, 1
Joe, 1, 2
Joe, 1, 3

Table B looks like
CustomerName, CustID, CustKey, Sales
Joe, 1, null, 10000

I want to insert the maximum CustKey value from Table A into the CustKey field of Table B... the result would be

Joe, 1, 3, 10000

I've tried something like the following, but no luck

update B b
set b.CustKey = (Select Max(CustKey) from A a where a.CustID= b.CustID)

Nick W Saban

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-16 : 10:36:50

If you want to use a table alias then you need to specify in a FROM clause:


update b set 
     b.CustKey = (Select Max(CustKey) from A a where a.CustID= b.CustID)
from	B as b

EDIT:
btw, from a design perspective, table A should not have custName. It already has the foreign key reference [CustKey] to your customer table [B].

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-16 : 10:38:17

[code]
update b
set CustKey = a.max_custkey
from tableb b
inner join
(
select CustID, max_custkey = max(CustKey)
from tablea
group by CustID
) a on b.CustID = a.CustID
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 13:59:43

[code]
update b
set CustKey = a.max_custkey
from tableb b
inner join
(
select distinct CustID,max(CustKey) over (partition by CustID) AS max_custkey
from tablea
) a on b.CustID = a.CustID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2011-10-16 : 22:38:02

Thanks very much for the answers!

Nick W Saban

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 01:29:01

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources