Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: subquery update help

    I need to update the status_date field in the journal table with the most recent status_date value in the transactions_batch_tmp.status_date column.
    However, when I run this query I get a syntax error:
    An aggregate may not appear in the set list of an UPDATE statement.

    UPDATE journal
    SET status_date = max(tmp.status_date)
    FROM transactions_batch_tmp tmp, contacts c, products p
    WHERE c.emailaddress = 'test@yahoo.com'
    AND c.contact_id = j.contact_id
    AND j.product_code = 'EAENERGY'
    AND j.product_code = p.product_code

    Can someone shed some light?

    TIA

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    UPDATE journal
    SET status_date = (SELECT max(tmp.status_date) FROM transactions_batch_tmp)
    FROM
    contacts c
    INNER JOIN journal j ON c.contact_id = j.contact_id
    INNER JOIN products p on j.product_code = p.product_code
    AND j.product_code = 'EAENERGY'
    WHERE
    c.emailaddress = 'test@yahoo.com'
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •