Login    Forum    Search    FAQ   Blog

Board index » Server Side Development » SQL




 Page 1 of 1 [ 5 posts ] 



Author Message
 Post subject: Updatin the query
 Post Posted: Thu Nov 19, 2009 6:59 am 
Offline

Joined: Sat Feb 21, 2009 1:03 pm
Posts: 47
I m havin two tables: I need to update unlocation_an in #t tab dat depends
on the unlocation_an in #edi_un_location_map_t table
But I need to take da unlocation_an value 4 da edi_partner if
available, otherwise 4m a general pool(where edi partner is null)
Please do help me in creatin da update query implement da above logic:
'
Code:
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)


Top 
 Post subject: Re: Updatin the query
 Post Posted: Thu Nov 19, 2009 6:59 am 
Offline

Joined: Sat Feb 21, 2009 1:03 pm
Posts: 47
OopSS!! i forget to indicate dat after update query was all
executed, I need to select * from #t must return da floowing
Code:
75850 USEZA
131265 USHOU
23802 USJAX
4502655 PRSJU



Top 
 Post subject: Re: Updatin the query
 Post Posted: Thu Nov 19, 2009 6:59 am 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
this?
Code:

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


Top 
 Post subject: Re: Updatin the query
 Post Posted: Thu Nov 19, 2009 7:00 am 
Offline

Joined: Sat Feb 21, 2009 1:03 pm
Posts: 47
thanks, dat waws wat I needed .


Top 
 Post subject: Re: Updatin the query
 Post Posted: Thu Nov 19, 2009 7:00 am 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
welcome


Top 
Display posts from previous:  Sort by  
 
 Page 1 of 1 [ 5 posts ] 




Board index » Server Side Development » SQL


Who is online

Users browsing this forum: No registered users and 1 guest

 
 

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron