UPDATE Problem

Expand Post »

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.

Oracle Syntax (Toggle Plain Text)
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
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

Similar Threads

Windows Update problem in Windows Vista and Windows 7
need help,,asp.net and sql update problem in ASP.NET
update problem???? in ASP.NET
System Formatted-Update problem in Windows NT / 2000 / XP
Firefox problem - can't install update and Thunderbird -"fights" with Netscape! in Windows NT / 2000 / XP

Roybut

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Oct 2008

Permalink

Aug 21st, 2009

Re: UPDATE Problem

Doh! I sorted it out myself:

Oracle Syntax (Toggle Plain Text)
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
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.

Last edited by Roybut; Aug 21st, 2009 at 9:57 am.

Roybut

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Oct 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: Help wit PLSQL

Next Thread in Oracle Forum Timeline: Sqldeveloper

DaniWeb Message

Cancel Changes

User Name
Password