PL/SQL: UPDATE query with multiple matches in WHERE clause

Question

TABLE_A:

ID      TYPE    DATE_UPLOADED   EXPIRED
9872    APPLE   03-JAN-11       0
9874    MANGO   03-JAN-11       0
9873    GRAPE   03-JAN-11       0

TABLE_B:

TYPE    LIFE
APPLE   3
MANGO   2
GRAPE   1

What I would like to achieve here is to update the EXPIRED
field of TABLE_A to a value of 1 when it's DATE_UPLOADED
has exceeded it's LIFE for that type as of the current date.

This update query is where I'm currently stuck with. I know its
wrong, that's where I need your help.

Update query:

UPDATE TABLE_A
SET EXPIRED = 1
WHERE EXPIRED = 0
AND (TRUNC(SYSDATE) - TRUNC(DATE_UPLOADED)) >
(
    SELECT LIFE
    FROM TABLE_B 
);

Do note that the TYPE field may be anything and may be more
than what is given in the sample data.

Possible duplicate of: http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join — Mike Nakis, Jan 04 '12 at 16:21
Thanks for the reference. Different problem but 'could' use the same solution. — cr8ivecodesmith, Jan 04 '12 at 16:23

score 4 · Accepted Answer · answered Jan 04 '12 at 16:11

4

You are not linking the two tables. Try something like this:

UPDATE TABLE_A
SET EXPIRED = 1
WHERE EXPIRED = 0
AND (TRUNC(SYSDATE) - TRUNC(DATE_UPLOADED)) >
(
    SELECT LIFE
    FROM TABLE_B 
    WHERE TYPE = TABLE_A.TYPE
);

answered Jan 04 '12 at 16:11

David M

69,925
13
153
183

1

That's not valid SQL for Oracle – a_horse_with_no_name Jan 04 '12 at 16:13
Good point. And it's not valid ANSI SQL either I believe. Edited. – David M Jan 04 '12 at 16:16
It's probably a good idea to use some aggregate in the subquery in order to ensure that only a single row is returned. Otherwise it might fail with an "single row query returned more than one row" error – a_horse_with_no_name Jan 04 '12 at 16:21
The question refers to LIFE in the singular for a particular TYPE, so my assumption was this would not be needed in this context. In general terms, yes, I agree. – David M Jan 04 '12 at 16:23
Thanks for this one! Saved me a lot of headache. – cr8ivecodesmith Jan 04 '12 at 16:26

PL/SQL: UPDATE query with multiple matches in WHERE clause

1 Answers1