How do I do an Oracle SQL update from select?

Question

I have created a query for updating a table from another table. The field is updating as expected for a given date. The problem is that for other dates, I am now getting NULL values on the column I updated. How do I set the new value only for the specific date and not affect the other dates?

Table A:

| ID | VALUE |      ADD_TIME        |
-------------------------------------
| 1  | -5    |1/11/2019 10:45:11 am |

Table B:

|AS_OF_DATE| ID | VALUE |
-------------------------
|2/29/2019 | 1  |  -4   |
|1/31/2019 | 1  |  -4   |
|12/31/2018| 1  |  -4   |

Desired Output:

|AS_OF_DATE| ID | VALUE |
-------------------------
|2/29/2019 | 1  |  -4   |
|1/31/2019 | 1  |  -4   |
|12/31/2018| 1  |  -5   |

Current Output:

|AS_OF_DATE| ID | VALUE |
-------------------------
|2/29/2019 | 1  |       |
|1/31/2019 | 1  |       |
|12/31/2018| 1  |  -5   |

My Query:

update TABLEB
set VALUE =
(
  select VALUE from TableA
  where ID = '1'
  and TABLEB.AS_OF_DATE < TABLEA.ADD_TIME
)

`TABLEA` has only one row? Than simple add `WHERE b.AS_OF_DATE < (select ADD_TIME from TABLEA)` to the UPDATE — Marmite Bomber, Apr 04 '19 at 14:23
Pls check your data types: `2/29/2019` is **not valid date**;) — Marmite Bomber, Apr 05 '19 at 08:18

Alex Poole · Accepted Answer · 2019-04-04T14:30:19.463

You could add an exists() check so rows which don't have a match aren't updated:

update TABLEB
set VALUE =
(
  select VALUE from TABLEA
  where ID = '1'
  and TABLEB.AS_OF_DATE < TABLEA.ADD_TIME
)
where exists
(
  select VALUE from TABLEA
  where ID = '1'
  and TABLEB.AS_OF_DATE < TABLEA.ADD_TIME
)

1 row updated.

select * from tableb;

AS_OF_DAT         ID      VALUE
--------- ---------- ----------
28-FEB-19          1         -4
31-JAN-19          1         -4
31-DEC-18          1         -5

I changed the 2/29/2019 to 2/28/2019 in your sample data...

Presumably you really have multiple IDs in both tables, so correlate on that column two - instead of WHERE ID = '1' (which should probably be WHERE ID = 1 anyway!) use WHERE TABLEB.ID = TABLEA.ID.

You could also use a merge instead of an update, e.g.:

merge into tableb b
using tablea a
on (a.id = b.id and b.as_of_date < a.add_time)
when matched then update set b.value = a.value;

or

merge into tableb b
using tablea a
on (a.id = b.id)
when matched then update set b.value = a.value
where b.as_of_date < a.add_time;

Thank you! And yes, I have multiple IDs and will make an update on that. For now, I was just trying to get one to work. I appreciate it :) — JMC, Apr 04 '19 at 17:43

score 0 · Answer 2 · answered Apr 05 '19 at 08:17

Your approach suggest, that the column IDis a primary key of the TABLEA.

Please check if this is realy true, as otheriwse you should take care with constructions such as:

set VALUE =
(
  select VALUE from Table A
  where ID = '1'
  and TABLE B.AS_OF_DATE < TABLE A.ADD_TIME
)

In case that the subquery returns more that one row, you will immediately see ORA-01427: single-row subquery returns more than one row

For primary key backed TABLEA you may use simple extension of your original query (adding a WHERE predicate):

update TABLEB 
set VALUE =
(
  select VALUE from TableA
  where ID = '1'
  and TABLEB.AS_OF_DATE < TABLEA.ADD_TIME
)
where TABLEB.AS_OF_DATE < (select ADD_TIME from TABLEA where ID = '1')

Note , that the duplicated row problem is relevant to the MERGE statement solution proposed in other answer, which works fine and elegant for unique ID in TABLEA but fails with ORA-30926: unable to get a stable set of rows in the source tables on duplicated IDs.

How do I do an Oracle SQL update from select?

2 Answers2