0

Hi,

I have a table which I want to update thus:

If d_date is has a value and b_date is 30 or more then set d_date as the min value already in d_date. I know that the WHERE EXISTS clause is wrong but I don't know how to make this work.

UPDATE table
SET d_date =
(
        SELECT min(d_date)
        FROM table
)
WHERE EXISTS
        (
                SELECT floor( MONTHS_BETWEEN (SYSDATE, b_date) / 12 )
                FROM agents
                WHERE floor( MONTHS_BETWEEN (SYSDATE, b_date) / 12 ) >=30
      ) 
AND d_date IS NOT NULL
1
Contributor
1
Reply
2
Views
8 Years
Discussion Span
Last Post by Roybut
0

Doh! I sorted it out myself:

UPDATE table
SET d_date =
(
        SELECT min(d_date)
        FROM table
)
WHERE
        floor( MONTHS_BETWEEN (SYSDATE, b_date) / 12 ) >=30
        AND d_date IS NOT NULL

I was making it more complicated than it was.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.