Sql Query for Updation on Selection -
i created 2 table follows
table forgerock:
id status amount name date 1 0 4500 ram 04/02/2012 2 0 2000 shyam 05/09/2013 4 0 1500 ghanshyam 08/06/2015 table forgerock1:
id status amount name date 3 0 4500 gopal 04/02/2012 2 0 8000 radheshyam 15/11/2013 4 1 1500 ghanshyam 08/06/2015 sql query
select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock f1 inner join forgerock1 f2 on f1.id = f2.id , f1.name=f2.name union select id, status, amount, name, date forgerock id not in (select distinct id forgerock1) union select id, status, amount, name, date forgerock1 id not in (select distinct id forgerock) union select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock f1 inner join forgerock1 f2 on f1.id = f2.id , (f1.name!=f2.name , f1.date!=f2.date) union select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock1 f1 inner join forgerock f2 on f1.id = f2.id , (f1.name!=f2.name , f1.date!=f2.date) result:
id status rank name date 4 1 1500 ghanshyam 08/06/2015 1 0 4500 ram 04/02/2012 3 0 4500 gopal 04/02/2012 2 0 8000 shyam 15/11/2013 2 0 2000 radheshyam 05/09/2013 now want update duplicate record id new id (i.e. in above result 2 duplicate,so change 1 record id 2 21)
in sql-server can try use row_number() duplicates , replace common table expression in following:
with cte1 ( select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock f1 inner join forgerock1 f2 on f1.id = f2.id , f1.name=f2.name union select id, status, amount, name, date forgerock id not in (select distinct id forgerock1) union select id, status, amount, name, date forgerock1 id not in (select distinct id forgerock) union select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock f1 inner join forgerock1 f2 on f1.id = f2.id , (f1.name!=f2.name , f1.date!=f2.date) union select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock1 f1 inner join forgerock f2 on f1.id = f2.id , (f1.name!=f2.name , f1.date!=f2.date) ), cte2 ( select id, [status], rank, name, [date], rn from( select id, [status], rank, name, [date], row_number() on (partition id order id) rn ( select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock f1 inner join forgerock1 f2 on f1.id = f2.id , f1.name=f2.name union select id, status, amount, name, date forgerock id not in (select distinct id forgerock1) union select id, status, amount, name, date forgerock1 id not in (select distinct id forgerock) union select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock f1 inner join forgerock1 f2 on f1.id = f2.id , (f1.name!=f2.name , f1.date!=f2.date) union select f1.id, case when f1.status = 1 f1.status else f2.status end status, case when f1.status = 1 f1.amount else f2.amount end rank, f1.name, case when f1.status = 1 f1.date else f2.date end date forgerock1 f1 inner join forgerock f2 on f1.id = f2.id , (f1.name!=f2.name , f1.date!=f2.date) )x ) x2 rn > 1 ) select case when cte1.id = cte2.id replace(cte1.id, cte2.id, cast(cte1.id nvarchar(20)) + '1') else cte1.id end id, cte1.[status], cte1.[rank], cte1.name, cte1.[date] cte1 left join cte2 on cte1.[rank] = cte2.[rank] , cte1.name = cte2.name output:
id status rank name date 4 1 1500 ghanshyam 2015-08-06 1 0 4500 ram 2012-04-02 3 0 4500 gopal 2012-04-02 2 0 8000 shyam 2013-09-11 21 0 2000 radheshyam 2013-05-09
Comments
Post a Comment