Help with Update and 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

Help with Update and Join?

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-12-12 : 10:23:57

I have a table with columns:
PersonName, Year, Address.

So there are lots of people, and each person has a 3 records, one for 2012, 2013, and 2014. ok?

Some 2014 addresses are currently NULL, and for those records, I want to fill in the 2013 address. I cannot figure out how to do this.
I got as far as:

Update myTable set Address = (
Select Address from myTable where Year=2013)
where Year = 2014 and Address is NULL

I understand why that's wrong but I don't know how to do it.
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-12-12 : 10:43:21

Give this a try:


update dt
set Adress = OldAdress
from
(
select
t1.PersonName, t1.Year, t1.Address, t2.Adress as OldAdress
from myTable as t1
join myTable as t2 
on t1.PersonName = t2.PersonName and 
   t1.Year = 2014 and 
   t2.Year = 2013 and 
   t1.Adress is null
) as dt

Too old to Rock'n'Roll too young to die.

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-12-12 : 13:04:19

quote:

Originally posted by webfred

Give this a try:


update dt
set Adress = OldAdress
from
(
select
t1.PersonName, t1.Year, t1.Address, t2.Adress as OldAdress
from myTable as t1
join myTable as t2 
on t1.PersonName = t2.PersonName and 
   t1.Year = 2014 and 
   t2.Year = 2013 and 
   t1.Adress is null
) as dt

Too old to Rock'n'Roll too young to die.

This didn't quite work. The intermediate table that it builds contains 119 rows, but when I run the entire query, affects all 24,000
rows. I tried to change it to the below (added line at end), but it says these items are ambiguous. How do I restrict it to only update the rows corresponding to the rows in the intermediate table?


update dt
set Adress = OldAdress
from
(
select
t1.PersonName, t1.Year, t1.Address, t2.Adress as OldAdress
from myTable as t1
join myTable as t2 
on t1.PersonName = t2.PersonName and 
   t1.Year = 2014 and 
   t2.Year = 2013 and 
   t1.Adress is null
) as dt

Where Year = 2014 and address is NULL

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-12-12 : 13:24:23

I got it to work.

I had changed dt in the first line to myTable.

I didn't realize that dt was the actual table, and doing updates to dt updates MyTable.

I thought dt was some kind of temporary table of data.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-12-12 : 13:42:39

Hi Dave,
sorry for being late but I was on my way home from work...

dt is the alias name for a derived table and you don't have to replace it anywhere in the given query.

(select column1, column2 from table1) as dt is a derived table named dt (it can also be named as KidRock or Elvis). That means you can treat the resultset of that query as a table.

Too old to Rock'n'Roll too young to die.

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-12-12 : 13:45:08

quote:
Originally posted by webfred

Hi Dave,
sorry for being late but I was on my way home from work...

dt is the alias name for a derived table and you don't have to replace it anywhere is the given query.

(select column1, column2 from table1) as dt is a derived table named dt (it can also be named as KidRock or Elvis). That means you can treat the resultset of that query as a table.

Too old to Rock'n'Roll too young to die.

Thanks. I need to read about "derived tables".

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-12-12 : 13:48:30

Glad you got it to work

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources