Updating records missing from clause

Question

With this query, I'm getting an error

ERROR:  missing FROM-clause entry for table "inv"
LINE 1: update stock_picking set invoice_status = inv.date_invoice

My goal is to set stock_picking posting_date to inv.date_invoice if it meets all conditions, is it possible with SQL?

update stock_picking set posting_date = inv.date_invoice
where id in 
(
SELECT DISTINCT sp.id
FROM stock_picking sp
                       INNER JOIN stock_move sm ON sp.id = sm.picking_id
                       INNER JOIN sale_order_line sol ON sm.sale_line_id = sol.id 
                       INNER JOIN sale_order so ON sol.order_id = so.id
                       INNER JOIN sale_order_invoice_rel so_inv_rel on so.id = so_inv_rel.order_id
                       INNER JOIN account_invoice inv on so_inv_rel.invoice_id = inv.id

    WHERE 

            sp.posting_date != inv.date_invoice
            and sm.posting_date != inv.date_invoice
            and sp.state = 'done'       
            and inv.state in ('open', 'paid')
            )

score 1 · Accepted Answer · edited Dec 04 '18 at 10:41

You can try below

update stock_picking S set posting_date = A.date_invoice
from 
(
SELECT DISTINCT sp.id, inv.date_invoice
FROM stock_picking sp
                       INNER JOIN stock_move sm ON sp.id = sm.picking_id
                       INNER JOIN sale_order_line sol ON sm.sale_line_id = sol.id 
                       INNER JOIN sale_order so ON sol.order_id = so.id
                       INNER JOIN sale_order_invoice_rel so_inv_rel on so.id = so_inv_rel.order_id
                       INNER JOIN account_invoice inv on so_inv_rel.invoice_id = inv.id

    WHERE 

            sp.posting_date != inv.date_invoice
            and sm.posting_date != inv.date_invoice
            and sp.state = 'done'       
            and inv.state in ('open', 'paid')
            ) A where S.id=A.id

ERROR: column reference "id" is ambiguous LINE 18: ) A where id=A.id ^ — Chaban33, Dec 04 '18 at 10:40

score 1 · Answer 2 · answered Dec 04 '18 at 10:44

You need a UPDATE ... FROM to be able to reference the column from inside the query in the update. Connect them in the WHERE clause later.

UPDATE stock_picking u SET posting_date = t.date_invoice
FROM (
  SELECT DISTINCT ON (sp.id) 
    sp.id, inv.date_invoice
  FROM stock_picking sp
  INNER JOIN stock_move sm ON sp.id = sm.picking_id
  INNER JOIN sale_order_line sol ON sm.sale_line_id = sol.id 
  INNER JOIN sale_order so ON sol.order_id = so.id
  INNER JOIN sale_order_invoice_rel so_inv_rel on so.id = so_inv_rel.order_id
  INNER JOIN account_invoice inv on so_inv_rel.invoice_id = inv.id
  WHERE 
    sp.posting_date != inv.date_invoice
    and sm.posting_date != inv.date_invoice
    and sp.state = 'done'       
    and inv.state in ('open', 'paid')
  ORDER BY sp.id, inv.date_invoice DESC
) t
WHERE u.id = t.id

Since I don't know your model, I've assumed that relationship between stock_picking and account_invoice may be 1:N thus using DISTINCT ON will return only one date_invoice for each id. You could use GROUP BY + MAX/MIN aggregate function to achieve the same result.

Notice the usage of t and u table aliases.

score 1 · Answer 3 · answered Dec 04 '18 at 10:44

You are getting the error as the update query is trying to get data from the inv table which isn't joined at all.

You are able to do joins on update queries as well.

update sp
set posting_date = inv.date_invoice
from stock_picking as sp
inner join account_invoice inv on ***whatever columns link the tables together***
where id in 
(
    SELECT DISTINCT sp.id
    FROM stock_picking sp
        INNER JOIN stock_move sm ON sp.id = sm.picking_id
        INNER JOIN sale_order_line sol ON sm.sale_line_id = sol.id 
        INNER JOIN sale_order so ON sol.order_id = so.id
        INNER JOIN sale_order_invoice_rel so_inv_rel on so.id = so_inv_rel.order_id
        INNER JOIN account_invoice inv on so_inv_rel.invoice_id = inv.id
    WHERE 
        sp.posting_date != inv.date_invoice
        and sm.posting_date != inv.date_invoice
        and sp.state = 'done'       
        and inv.state in ('open', 'paid')
)

Updating records missing from clause

3 Answers3