May 13, 2013 at 9:44 am
I have 2 tables as following. i am trying to do the following update to the 2nd table (Address)
1) Check if there are any records in the address table with 'Addtype' value = 'To' and all four address columns are null (Example ID=9)
2) If exists then use table 1 to find a from address ID, copy all the four address columns for from_ID and update those values to the to_ID
what is the best way to do it?
----------------------------------------------------------------
following are temp tables with table structures and sample data
CREATE TABLE #List (From_ID int , To_ID int)
insert into #List values(1,7)
insert into #List values (3,9)
select * From #List
CREATE TABLE #Addredss (ID INT , Address1 varchar (50), Address2 varchar(50), city varchar(50), stateAdd varchar (50), AddType varchar (10))
insert into #Addredss (ID,AddType) values (1,'From')
insert into #Addredss values(7,'test1','test1','city1','state1','To')
insert into #Addredss values (3,'test2','test2','city2','state2','From')
insert into #Addredss (ID,AddType) values (9,'To')
insert into #Addredss values (2,'test3','test3','city3','state3','To')
select * from #Addredss
drop table #List
drop table #Addredss
------------------------------------------------------------------
Any help is appreciated .
Thanks,
Alvin
May 13, 2013 at 9:53 am
Great job posting ddl and sample data!!!
This should accomplish what you are after.
update a
set Address1 = a2.Address1,
Address2 = a2.Address2,
city = a2.city,
stateAdd = a2.stateAdd
from #List l
join #Addredss a on a.ID = l.To_ID
join #Addredss a2 on a2.ID = l.From_ID
where a.AddType = 'To'
and a.Address1 is null
and a.Address2 is null
and a.city is null
and a.stateAdd is null
select * from #Addredss
--EDIT--
Missed the condition for the AddType to be "to"
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2013 at 9:55 am
This is a pretty simple way to do it:
update b
set Address1 = a.Address1,
Address2 = a.Address2,
city = a.city,
stateAdd = a.stateAdd
from
(select * From #List join #Addredss on From_ID = ID) a
join
(select * From #Addredss
where AddType = 'To' and Address1 is null and Address2 is null and city is null and stateAdd is null) b
on a.To_ID = b.ID
You are basically creating to tables here: one that joins #List and #Addredss on the From_ID value, the other that gives you those in the #Addredss that don't have the values. You then update the second table using the data provided from the first table (address information for the From_ID.
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
May 13, 2013 at 10:01 am
Awesome! both solutions work good. thank you very much guys.
Alvin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply