-2

I have this error when i try to do an update with a inner:

ERROR:  syntax error at or near "INNER"
LINE 1: UPDATE sale_order AS so SET state='progress' INNER JOIN sale...
                                                     ^

the syntax is : 

UPDATE sale_order AS so 
SET so.state='progress' 
INNER JOIN sale_order_invoice_rel AS soi ON so.id= soi.order_id 
INNER JOIN account_invoice AS ai ON soi.invoice_id=ai.id
WHERE so.state='done' AND ai.state !='paid';

the schema of the table is :

  • sale_order : id | state
  • sale_order_invoice_rel : id | order_id |invoice_id
  • account_invoice : id | state
J1b4y
  • 1
  • 1
  • 1
  • http://www.postgresql.org/docs/current/static/sql-update.html –  Jan 13 '15 at 15:56
  • Sorry but this isn't useful my problem is on th join and the documention didn't help , instead thanks for the help – J1b4y Feb 12 '15 at 10:50
  • 1
    Why didn't the documentation help? Where does it say you can write what you wrote? – philipxy May 28 '19 at 12:07

1 Answers1

0

The postgres update syntax is:

  • UPDATE target_table
  • SET ...
  • FROM other_table
  • [ optional JOIN ... ON ... ]
  • WHERE ...
UPDATE sale_order AS so 
SET state = 'progress' 
FROM sale_order_invoice_rel AS soi ON so.id = soi.order_id -- <<-- this line
JOIN account_invoice AS ai ON soi.invoice_id = ai.id
WHERE so.state = 'done' AND ai.state <> 'paid' -- <<-- this line
  ;

Updated (removed correlation name from SET so.state = ... -> SET state = ...)

UPDATE2 (oops!) 2015-02-12 (move the ON ... to the WHERE ... clause)

UPDATE sale_order AS so 
SET state = 'progress' 
FROM sale_order_invoice_rel AS soi
JOIN account_invoice AS ai ON soi.invoice_id = ai.id
WHERE so.id = soi.order_id -- <<-- this line
AND so.state = 'done' AND ai.state <> 'paid'
  ;
joop
  • 4,330
  • 1
  • 15
  • 26
  • Thank you for you help but this didn't work too : UPDATE sale_order AS so SET so.state = 'progress' FROM sale_order_invoice_rel AS soi ON so.id = soi.order_id JOIN account_invoice AS ai ON soi.invoice_id = ai.id WHERE so.state = 'done' AND ai.state <> 'paid' ; ERROR: syntax error at or near "ON" LINE 3: FROM sale_order_invoice_rel AS soi ON so.id = soi.order_id – J1b4y Jan 15 '15 at 13:20
  • `SET so.state='progress' ` You should not use an alias/correlation name at the left side of the assignment --> `SET state='progress' ` – joop Feb 12 '15 at 11:33
  • UPDATE sale_order AS so SET state = 'progress' FROM sale_order_invoice_rel AS soi ON so.id = soi.order_id JOIN account_invoice AS ai ON soi.invoice_id = ai.id WHERE so.state = 'done' AND ai.state <> 'paid' ; ERROR: syntax error at or near "ON" LINE 3: FROM sale_order_invoice_rel AS soi ON so.id = soi.order_id – J1b4y Feb 12 '15 at 14:45
  • Oops, I errord when converting your `JOIN` to a `FROM` . – joop Feb 12 '15 at 15:13