CODEWITHSUNDEEP
sqlpostgresql
Chaban33
Chaban33

Reputation: 1382

Update ids that are select

I making this query and it returns me 88 ids 

SELECT DISTINCT so.id
FROM stock_picking sp
                       INNER JOIN stock_move sm ON sp.id = sm.picking_id
                       INNER JOIN procurement_order po ON sm.procurement_id = po.id
                       INNER JOIN sale_order_line sol ON po.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 

            so.invoice_status = 'to invoice'
            and sp.state = 'done'       
            and inv.state != 'draft'

now I want to update these ids and I tried it like this but I get an error, how can I do it correctly?

instead of select, i tried this line

update so set invoice_status = 'invoiced' but get an error

ERROR:  relation "so" does not exist
LINE 1: update so set invoice_status = 'invoiced'

Upvotes: 0

Views: 41

Answers (1)

Boris Jovanovic
Boris Jovanovic

Reputation: 132

[so] is an alias that you set up for the sale_order table and the code can only recognize it in a query which you set up the alias in. That being said if you ran the update query that you wanted you update all of the lines in the sale_order table. To update the table for only the id's that are return by the SELECT query about use the following:

update sale_order set invoice_status = 'invoiced' 
where id in 
(
SELECT DISTINCT so.id
FROM stock_picking sp
                   INNER JOIN stock_move sm ON sp.id = sm.picking_id
                   INNER JOIN procurement_order po ON sm.procurement_id = po.id
                   INNER JOIN sale_order_line sol ON po.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 

        so.invoice_status = 'to invoice'
        and sp.state = 'done'       
        and inv.state != 'draft'
)

Upvotes: 1

Related Questions