PL / SQL: UPDATE query with multiple matches in a 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 get here is to update EXPIRED
field TABLE_A to value 1 when it is DATE_UPLOADED
exceeded its LIFE for this type from the current date.

This update request is where I am stuck right now. I know him wrong that where I need your help.

Refresh request:

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

Note that the TYPE field can be anything and can be more than what is given in the data sample.

Answer 1

You do not link 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 );