Update statment

Database

Update statment

Hi 

can any one share the logic for the Update statment. i am not able to do the update records.

i want to remove the duplicate records from the table based on the Max date

site,part1,part2,part3,mfg_date,upd_date

1,11,22,33, '2014-06-04' , '9999-12-31' 

1,11,22,33, '2014-05-02'  '9999-12-31' 

1,11,22,33, '2014-03-01'  '9999-12-31' 

2,10,20,30, '2014-06-30'  '9999-12-31' 

2,10,20,30, '2014-05-15'  '9999-12-31' 

3,11,22,33, '2014-06-05'  '9999-12-31' 

Select site,part1,part2,pasrt3,Max(mfg_date)  

From Table

Group by site,part1,part2,pasrt3

need help in Update statment. Please share the Logic

Update Table

Set upd_date = Sysdate -1

Where mfg_date Not in 

(

Select Max(mfg_date)  

From Table

Group by site,part1,part2,part3

)

Thanks

2 REPLIES

Re: Update statment

update b  from 
dup_test b,(sel site,part1,part2,part3,mfg_date,rank ()over (partition by site,part1,part2,part3 order by mfg_date desc) rnk from dup_test qualify rnk<>1) a
set
upd_date=current_date-1
where
b.site=a.site and
b.part1=a.part1 and
b.part2=a.part2 and
b.part3=a.part3 and
b.mfg_date=a.mfg_date

Re: Update statment

@krishaneesh

Thanks for the Query and this is really helps me thinking in another way

i have tried this in the above query in my post works fine but i am facing the issue with Null values also to be picked and Update

current i am using the coalesce  function.

Update Table_1 A
Set upd_date = Sysdate -1
Where mfg_date Not in
(
Select Max(mfg_date)
From Table_1 B
Where B.site = A.site
and B.part1 = A.part1
and B.part2 = A.part3
Group by site,part1,part2,part3
)

can you share the logic for the Update the same 

issue is 

site,part1,part2,part3,mfg_date,upd_date
1,11,22,33, '2014-06-04' , '9999-12-31'
1,11,22,33, '2014-05-02' '9999-12-31'
1,11,22,33, '2014-03-01' '9999-12-31'
2,10,20,30, '2014-06-30' '9999-12-31'
2,10,20,30, '2014-05-15' '9999-12-31'
3,11,22,33, '2014-06-05' '9999-12-31'

Select site,part1,part2,pasrt3,Max(mfg_date)
From Parts
Group by site,part1,part2,pasrt3
Order by mfg_date

OutPUT
-----

site,part1,part2,part3,mfg_date,upd_date
1,11,22,33, '2014-03-01' '2014-05-02'
1,11,22,33, '2014-05-02' '2014-06-04'
1,11,22,33, '2014-06-04' '9999-12-31'

2,10,20,30, '2014-05-15' '2014-06-30'
2,10,20,30, '2014-06-30' '9999-12-31'

mfg_date upd_date
-------- -----------

'2014-06-04' --> '9999-12-31
'2014-05-02' --> '2014-06-04'
'2014-03-01' --> '2014-05-02'

Thanks