4

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.

John Doyle
  • 7,475
  • 5
  • 33
  • 40
cr8ivecodesmith
  • 2,021
  • 5
  • 21
  • 30

1 Answers1

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
);
David M
  • 71,481
  • 13
  • 158
  • 186
  • 1
    That's not valid SQL for Oracle –  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 –  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