Post reply

Finding the greater value among the lesser ones in other table

  • June 3, 2016 at 8:09 am

    #311042

    Coming from ORACLE world, I'm trying to fill a DATETIME field <MyDate> in TableA with the highest value of the field <xDate> in TableB among the ones that are lesser than <OtherDate> in Table A.

    UPDATE X SET TA.MyDate = (SELECT MAX(xDate) FROM TableB WHERE xDate<=TA.OtherDate) FROM TableA AS TA

    But unfortunately this query doesn't work and I get an error: "An aggregate may not appear in the set list of an UPDATE statement."

    Any idea?

    Thanks in advance

  • June 3, 2016 at 9:32 am

    #1883132

    I don't get that error with a correction on your code.

    --Create sample data

    CREATE TABLE TableA( MyDate date, OtherDate date);

    CREATE TABLE TableB( xDate date);

    INSERT INTO TableA(OtherDate) VALUES(GETDATE()), (GETDATE()-1), (GETDATE() - 2);

    INSERT INTO TableB(xDate) VALUES(GETDATE()-1);

    --Verify sample data

    SELECT * FROM TableA;

    UPDATE TA SET

    TA.MyDate = (SELECT MAX(xDate) FROM TableB WHERE xDate<=TA.OtherDate)

    FROM TableA AS TA;

    --Verify results

    SELECT * FROM TableA;

    GO

    --Clean my db

    DROP TABLE TableA;

    DROP TABLE TableB;

    Although, I don't like to include subqueries in the column list. I'd use a CROSS APPLY.

    UPDATE TA SET

    TA.MyDate = TB.MaxxDate

    FROM TableA AS TA

    CROSS APPLY (SELECT MAX(xDate) MaxxDate FROM TableB WHERE xDate<=TA.OtherDate) TB;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 3, 2016 at 2:08 pm

    #1883200

    Thank you very much Luis. I really appreciate your quick response & valuable help.

    You're right.

    Now I'm going to learn a bit about APPLY.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply