SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Update and Join?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DaveBF
Yak Posting Veteran

84 Posts

Posted - 12/12/2013 :  10:23:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 12/12/2013 :  10:43:21  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

DaveBF
Yak Posting Veteran

84 Posts

Posted - 12/12/2013 :  13:04:19  Show Profile  Reply with Quote
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


Go to Top of Page

DaveBF
Yak Posting Veteran

84 Posts

Posted - 12/12/2013 :  13:24:23  Show Profile  Reply with Quote
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.



Edited by - DaveBF on 12/12/2013 13:42:49
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 12/12/2013 :  13:42:39  Show Profile  Visit webfred's Homepage  Reply with Quote
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.

Edited by - webfred on 12/12/2013 13:44:03
Go to Top of Page

DaveBF
Yak Posting Veteran

84 Posts

Posted - 12/12/2013 :  13:45:08  Show Profile  Reply with Quote
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".
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 12/12/2013 :  13:48:30  Show Profile  Visit webfred's Homepage  Reply with Quote
Glad you got it to work


Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000