Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Problem in Update with select

    Hi
    Have this update query:

    Code:
    UPDATE kanban_data kd SET (wip_chemicalweighing, wip_compounding, wip_extrusion, wip_forming, wip_deflashing) = (SELECT ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing, ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding, ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion', 'ROUND(SUM(deflashing),2) AS wip_deflashing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM);
    and I got this error:

    Error Code : 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(wip_chemicalweighing, wip_compounding, wip_extrusion, wip_forming, wip_deflashi' at line 1
    (16 ms taken)

    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you cannot SET a parenthesised list of columns

    you can only SET single columns, one at a time

    so what you want is...
    Code:
    SET column1 = expression
      , column2 = expression
      , column3 = expression
    etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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