Update query - 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 2005 Forums

Transact-SQL (2005)

Update query

Author

Topic

mgandra
Starting Member

15 Posts

Posted - 2009-10-08 : 15:45:15

I have two tables: I want to update unlocation_an in #t table based on unlocation_an in #edi_un_location_map_t table
But I want to take the unlocation_an value for the edi_partner if available, otherwise from general pool(where edi partner is null)
Please help me in creating the update query to implement the above logic:

create table #t(location_id int, edi_partner_id int, unlocation_an varchar(10))
insert into #t(location_id, edi_partner_id) values (75850,97)
insert into #t (location_id, edi_partner_id) values (131265,97)
insert into #t(location_id, edi_partner_id) values(23802,97)
insert into #t(location_id, edi_partner_id) values(4502655,97)

create table #edi_un_location_map_t(location_id int, unlocation_an varchar(10), ediPartner_id int)
insert into #edi_un_location_map_t values (75850, 'USEWR',null)
insert into #edi_un_location_map_t values (131265, 'USHOU',null)
insert into #edi_un_location_map_t values (23802, 'USJAX',null)
insert into #edi_un_location_map_t values (4502655, 'PRSJU',null)
insert into #edi_un_location_map_t values (75850, 'USEZA',97)

mgandra
Starting Member

15 Posts

Posted - 2009-10-08 : 15:56:52

I forgot to indicate taht after update query is executed, I want select * from #t should return the floowing
75850 USEZA
131265 USHOU
23802 USJAX
4502655 PRSJU

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-08 : 16:09:29

this?

update a
set a.unlocation_an = b.unlocation_an 
from #t a
inner join (select *,row_number() over(partition by location_id order by ediPartner_id desc) as seq from #edi_un_location_map_t) b
on a.location_id = b.location_id
where b.seq = 1

mgandra
Starting Member

15 Posts

Posted - 2009-10-08 : 16:13:52

Thanks, that's what I need.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-08 : 16:19:05

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources