Skip to Main Content
  • Questions

  • Update based on the result row count, always first row should be updated with the value as Y and the rest with N.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Venkat.

Asked: June 22, 2016 - 6:07 am UTC

Last updated: June 22, 2016 - 6:56 am UTC

Version: 10g

Viewed 1000+ times

You Asked

Update TABLE_ORDER SET LATEST_ORDER= 'Y' WHERE ORDER_ID = XXX ORDER BY ORDER_PLACED_TIME DESC;

Lets say the above query updates 10 rows, I want the LATEST_ORDER value as Y for the first row and rest of the 9 rows should be updated with N. Is it possible to do it in one UPDATE query ?


and Connor said...


SQL> create table t ( order_id int, order_date date, latest varchar2(1));

Table created.

SQL>
SQL> insert into t
  2  select trunc(rownum/10)+1, sysdate+rownum, null
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL>
SQL> select * from t order by 1,2;

  ORDER_ID ORDER_DAT L
---------- --------- -
         1 23-JUN-16
         1 24-JUN-16
         1 25-JUN-16
         1 26-JUN-16
         1 27-JUN-16
         1 28-JUN-16
         1 29-JUN-16
         1 30-JUN-16
         1 01-JUL-16
         2 02-JUL-16
         2 03-JUL-16
         2 04-JUL-16
         2 05-JUL-16
         2 06-JUL-16
         2 07-JUL-16
         2 08-JUL-16
         2 09-JUL-16
         2 10-JUL-16
         2 11-JUL-16
         3 12-JUL-16
         3 13-JUL-16
         3 14-JUL-16
         3 15-JUL-16
         3 16-JUL-16
         3 17-JUL-16
         3 18-JUL-16
         3 19-JUL-16
         3 20-JUL-16
         3 21-JUL-16
         4 22-JUL-16
         4 23-JUL-16
         4 24-JUL-16
         4 25-JUL-16
         4 26-JUL-16
         4 27-JUL-16
         4 28-JUL-16
         4 29-JUL-16
         4 30-JUL-16
         4 31-JUL-16
         5 01-AUG-16
         5 02-AUG-16
         5 03-AUG-16
         5 04-AUG-16
         5 05-AUG-16
         5 06-AUG-16
         5 07-AUG-16
         5 08-AUG-16
         5 09-AUG-16
         5 10-AUG-16
         6 11-AUG-16
         6 12-AUG-16
         6 13-AUG-16
         6 14-AUG-16
         6 15-AUG-16
         6 16-AUG-16
         6 17-AUG-16
         6 18-AUG-16
         6 19-AUG-16
         6 20-AUG-16
         7 21-AUG-16
         7 22-AUG-16
         7 23-AUG-16
         7 24-AUG-16
         7 25-AUG-16
         7 26-AUG-16
         7 27-AUG-16
         7 28-AUG-16
         7 29-AUG-16
         7 30-AUG-16
         8 31-AUG-16
         8 01-SEP-16
         8 02-SEP-16
         8 03-SEP-16
         8 04-SEP-16
         8 05-SEP-16
         8 06-SEP-16
         8 07-SEP-16
         8 08-SEP-16
         8 09-SEP-16
         9 10-SEP-16
         9 11-SEP-16
         9 12-SEP-16
         9 13-SEP-16
         9 14-SEP-16
         9 15-SEP-16
         9 16-SEP-16
         9 17-SEP-16
         9 18-SEP-16
         9 19-SEP-16
        10 20-SEP-16
        10 21-SEP-16
        10 22-SEP-16
        10 23-SEP-16
        10 24-SEP-16
        10 25-SEP-16
        10 26-SEP-16
        10 27-SEP-16
        10 28-SEP-16
        10 29-SEP-16
        11 30-SEP-16

100 rows selected.

SQL>
SQL> update t t_outer
  2  set latest =
  3       ( select decode(count(*),0,'Y','N')
  4         from   t t_inner
  5         where  t_inner.order_id = t_outer.order_Id
  6         and    t_inner.order_date > t_outer.order_date )
  7  /

100 rows updated.

SQL>
SQL> select * from t order by 1,2;

  ORDER_ID ORDER_DAT L
---------- --------- -
         1 23-JUN-16 N
         1 24-JUN-16 N
         1 25-JUN-16 N
         1 26-JUN-16 N
         1 27-JUN-16 N
         1 28-JUN-16 N
         1 29-JUN-16 N
         1 30-JUN-16 N
         1 01-JUL-16 Y
         2 02-JUL-16 N
         2 03-JUL-16 N
         2 04-JUL-16 N
         2 05-JUL-16 N
         2 06-JUL-16 N
         2 07-JUL-16 N
         2 08-JUL-16 N
         2 09-JUL-16 N
         2 10-JUL-16 N
         2 11-JUL-16 Y
         3 12-JUL-16 N
         3 13-JUL-16 N
         3 14-JUL-16 N
         3 15-JUL-16 N
         3 16-JUL-16 N
         3 17-JUL-16 N
         3 18-JUL-16 N
         3 19-JUL-16 N
         3 20-JUL-16 N
         3 21-JUL-16 Y
         4 22-JUL-16 N
         4 23-JUL-16 N
         4 24-JUL-16 N
         4 25-JUL-16 N
         4 26-JUL-16 N
         4 27-JUL-16 N
         4 28-JUL-16 N
         4 29-JUL-16 N
         4 30-JUL-16 N
         4 31-JUL-16 Y
         5 01-AUG-16 N
         5 02-AUG-16 N
         5 03-AUG-16 N
         5 04-AUG-16 N
         5 05-AUG-16 N
         5 06-AUG-16 N
         5 07-AUG-16 N
         5 08-AUG-16 N
         5 09-AUG-16 N
         5 10-AUG-16 Y
         6 11-AUG-16 N
         6 12-AUG-16 N
         6 13-AUG-16 N
         6 14-AUG-16 N
         6 15-AUG-16 N
         6 16-AUG-16 N
         6 17-AUG-16 N
         6 18-AUG-16 N
         6 19-AUG-16 N
         6 20-AUG-16 Y
         7 21-AUG-16 N
         7 22-AUG-16 N
         7 23-AUG-16 N
         7 24-AUG-16 N
         7 25-AUG-16 N
         7 26-AUG-16 N
         7 27-AUG-16 N
         7 28-AUG-16 N
         7 29-AUG-16 N
         7 30-AUG-16 Y
         8 31-AUG-16 N
         8 01-SEP-16 N
         8 02-SEP-16 N
         8 03-SEP-16 N
         8 04-SEP-16 N
         8 05-SEP-16 N
         8 06-SEP-16 N
         8 07-SEP-16 N
         8 08-SEP-16 N
         8 09-SEP-16 Y
         9 10-SEP-16 N
         9 11-SEP-16 N
         9 12-SEP-16 N
         9 13-SEP-16 N
         9 14-SEP-16 N
         9 15-SEP-16 N
         9 16-SEP-16 N
         9 17-SEP-16 N
         9 18-SEP-16 N
         9 19-SEP-16 Y
        10 20-SEP-16 N
        10 21-SEP-16 N
        10 22-SEP-16 N
        10 23-SEP-16 N
        10 24-SEP-16 N
        10 25-SEP-16 N
        10 26-SEP-16 N
        10 27-SEP-16 N
        10 28-SEP-16 N
        10 29-SEP-16 Y
        11 30-SEP-16 Y

100 rows selected.

SQL>
SQL>
SQL>


It might a little more careful inspection for things like tied dates etc, but you get the idea.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Or. ..

Ghassan, June 22, 2016 - 8:43 am UTC

Merge into t tgt
Using..
(Select order _ date, ..., max (order_date) over ..order.. as maxdate
From t
) src
On I'd --- or rowid if you hint above the rowid hint.
WHEN matched
Set .. = case on date = max date then yes else no
....

This gives me an idea.

A reader, June 22, 2016 - 10:09 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.