Member Avatar for Roybut

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
  • 134 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Roybut
Member Avatar for Roybut

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.