Compare and update sql server query

I have a table with following fields

updateto   hb     AD   AdDate               Hub    HubDate

                    1        5         8/4/2016          null    null
                    6        null     null                   8         8/5/2016
                    7        7          8/1/2016         5         8/4/2016
                    11      11        8/3/2016         11       5/4/2016

so we will need to update the "updateto" field following way

for row 1) we compare AD and hub and since hub is null updateto = AD
row 2 ) we compare AD with hub and since AD is null   updateto = hub
row 3 ) we compare AD and hub with hb . since AD matches hb  updateto = AD
row 4 ) we compare AD and hub with hb and since they all three are same we compare addate with hubdate and since hubdate is higher  updateto = hub


there are more then 4 rows in the table. I have just given an example over here.

how do i write a sql statement to make this happen.
PratikShah111Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
UPDATE YourTable
SET updateto = CASE 
   WHEN hub IS NULL THEN AD
   WHEN AD IS NULL THEN hub
   WHEN AD = hub AND hub <> hb THEN AD
   WHEN AD = hub AND hub = hb AND addate < hubdate THEN hub END

Open in new window

For more info on CASE blocks check out SQL Server CASE Solutions
0
 
ZberteocCommented:
Try this:
--/*
if object_id('tempdb..#temp') is not null
    drop table #temp
create table #temp(updateto varchar(100), hb int, AD int, AddDate date, Hub int, HubDate date)
insert into #temp(hb, AD, AddDate, Hub, HubDate)
select * from
    (values
	   (1,        5,         '8/4/2016',          null,    null),
	   (6,        null,     null,                   8,         '8/5/2016'),
	   (7,        7,          '8/1/2016',         5,         '8/4/2016'),
	   (11,      11,        '8/3/2016',         11,       '5/4/2016')
    ) a(hb, AD, AdDate, Hub, HubDate)
--*/

select * from #temp

update #temp set
    updateto=
	   case 
		  when hub is null then AD
		  when AD is null then Hub
		  when AD=Hub then AD
		  when AD=Hub and Hub=hb and HubDate>AddDate then Hub 
		  else AD
    end


select * from #temp

Open in new window

0
 
Olaf DoschkeSoftware DeveloperCommented:
In regard to the rules theres just one case missing, what if AD = hub AND hub = hb AND addate >= hubdate, I'd say that'll mean AD again. So

WHEN AD = hub AND hub = hb AND addate < hubdate THEN hub ELSE AD END

Bye, Olaf.
0
 
ZberteocCommented:
@Olaf,

This has nothing to do with the question.

Every time I read that "Bye, Olaf" ending formula it feels so definitive, like you will never come back, but still you do. :o)))

Talk to you later, Zberteoc. :o)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's happening when AD and HUB are both NULL?
0
All Courses

From novice to tech pro — start learning today.