CODEWITHSUNDEEP
sqloracle-database
Justiciar
Justiciar

Reputation: 376

Oracle SQL - update 2 columns in row with the oldest date

I am attempting to update 2 columns in a row. The row that should be updated is the row that has the oldest duedate

The table chorecompletion is described as: 

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CHOREID                                   NOT NULL NUMBER(38)
 GROUPID                                   NOT NULL NUMBER(38)
 DUEDATE                                   NOT NULL DATE
 COMPLETEDDATE                                      DATE
 COMPLETEDBY                                        NUMBER(38)

This query returns the row that I want to update

select *
from 
(
        select choreid, duedate, row_number() 
        over (partition by choreid order by duedate) as rn 
        from chorecompletion where choreid = 12 and groupid = 6
)
where rn = 1;

Where I could use some help is how to use this query in my update statement, specifically my where clause

my current attempt:

update chorecompletion 
set completeddate = sysdate, completedby=1
where --How to get the result of the previous query here?

Any help on my logic would be hugely appreciated.

Example desired result:

Before Update:

CHOREID      GROUPID    DUEDATE     COMPLETEDDATE      COMPLETEDBY
-------------------------------------------------------------------
  12          6        2018-11-1
  12          6        2018-10-1

After Update

CHOREID      GROUPID    DUEDATE     COMPLETEDDATE      COMPLETEDBY
-------------------------------------------------------------------
  12          6        2018-11-1 
  12          6        2018-10-1      2018-09-30            1

Upvotes: 1

Views: 290

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You can use a correlated subquery. If I understand correctly:

update chorecompletion
    set completeddate = (select min(duedate)
                         from chorecompletion cc
                         where cc.choreid = chorecompletion.coreid
                        )
    where choreid = 12 and groupid = 6

Upvotes: 0

MT0
MT0

Reputation: 168361

You can use a MERGE statement and can join on the ROWID pseudo-column so that you can correlated directly to the matched row: SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE chorecompletion ( choreid, groupid, duedate, completeddate, completedby ) AS
  SELECT 12, 6, DATE '2018-09-29', CAST( null AS DATE ), CAST( null AS NUMBER ) FROM DUAL UNION ALL
  SELECT 12, 6, DATE '2018-09-30', null, null FROM DUAL;

Query 1:

MERGE INTO chorecompletion dst
USING (
  SELECT ROWID AS rid
  FROM   (
    SELECT *
    FROM   chorecompletion
    WHERE  choreid = 12
    AND    groupid = 6
    ORDER BY duedate ASC
  )
  WHERE ROWNUM = 1
) src
ON ( src.RID = dst.ROWID )
WHEN MATCHED THEN
  UPDATE
  SET completeddate = sysdate,
      completedby   = 1

Results:

1 Row Updated.

Query 2:

SELECT * FROM chorecompletion

Results:

| CHOREID | GROUPID |              DUEDATE |        COMPLETEDDATE | COMPLETEDBY |
|---------|---------|----------------------|----------------------|-------------|
|      12 |       6 | 2018-09-29T00:00:00Z | 2018-09-30T18:42:45Z |           1 |
|      12 |       6 | 2018-09-30T00:00:00Z |               (null) |      (null) |

Query 3: You could also use an UPDATE statement with the ROWID pseudo-column:

UPDATE chorecompletion dst
SET    completeddate = sysdate,
       completedby   = 2
WHERE  ROWID = (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER ( PARTITION BY choreid ORDER BY duedate ) rn
    FROM   chorecompletion
    WHERE  choreid = 12
    AND    groupid = 6
    ORDER BY duedate ASC
  )
  WHERE rn = 1
)

Results:

1 Row Updated.

Query 4:

SELECT * FROM chorecompletion

Results:

| CHOREID | GROUPID |              DUEDATE |        COMPLETEDDATE | COMPLETEDBY |
|---------|---------|----------------------|----------------------|-------------|
|      12 |       6 | 2018-09-29T00:00:00Z | 2018-09-30T18:42:45Z |           2 |
|      12 |       6 | 2018-09-30T00:00:00Z |               (null) |      (null) |

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143013

Something like this?

SQL> create table test
  2  (choreid number,
  3   groupid number,
  4   duedate date,
  5   completeddate date,
  6   completedby number
  7  );

Table created.

SQL> insert into test
  2    select 12, 6, date '2018-01-11', null, null from dual union all
  3    select 12, 6, date '2018-01-10', null, null from dual;

2 rows created.

SQL> update test t set
  2    t.completeddate = sysdate,
  3    t.completedby = 1
  4  where t.duedate = (select min(t1.duedate)
  5                     from test t1
  6                     where t1.choreid = t.choreid
  7                       and t1.groupid = t.groupid)
  8    and t.choreid = 12
  9    and t.groupid = 6;

1 row updated.

SQL> select * From test;

   CHOREID    GROUPID DUEDATE    COMPLETEDD COMPLETEDBY
---------- ---------- ---------- ---------- -----------
        12          6 2018-01-11
        12          6 2018-01-10 2018-09-30           1

SQL>

Upvotes: 2

Related Questions