CODEWITHSUNDEEP
sqloracle-database
uzhas
uzhas

Reputation: 925

how to update all rows except first?

I have this sql query :

update CCUSTOMERINFO set VALIDTO=sysdate where (
select * from (
select row_number() over (order by created desc) rn, customer_id, CCUSTOMERINFO.VALIDTO
from CCUSTOMERINFO
where customer_id=100309772  order by created DESC) where rn > 1);

But it say it have some mistake.

This query returns all i want to update :

 select * from (
    select row_number() over (order by created desc) rn, customer_id, CCUSTOMERINFO.VALIDTO
    from CCUSTOMERINFO
    where customer_id=100309772  order by created DESC) where rn > 1)

Any suggestion how can i do that?

Upvotes: 1

Views: 703

Answers (1)

psaraj12
psaraj12

Reputation: 5072

Use it like the below

  update CCUSTOMERINFO set VALIDTO=sysdate where rowid in (
  select row_id from (
  select row_number() over (order by created desc) rn, customer_id, rowid row_id,
  CCUSTOMERINFO.VALIDTO
  from CCUSTOMERINFO
  where customer_id=100309772  order by created DESC) where rn > 1);

Upvotes: 3

Related Questions