-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
a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
J1b4y
  • 1
  • 1
  • 1

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,134
  • 1
  • 13
  • 24
  • 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