Question 278042 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update with Join for Postgre SQL

I’m trying to update a table by using join. Also one of the join is self join.

I need to update a column with the same column in the same table.

SERVICE ID code       value
123        QUANTITY1  2
123        QUANTITY2  null

It should be update QUANTITY2 as "2".

UPDATE "X"."ORDER_PRODUCT" op
set op."PROPERTY_VALUE"= op2."PROPERTY_VALUE" 
from "X"."ORDER_PRODUCT" op2  cross join 
"X"."ORDER_PROJECT" op3
where op."SERVICE_ID"= op2."SERVICE_ID" and op."ORDER_ID" =op3."ID" 
and op."PRODUCT_CODE" ='Tell_Datacenter_Co-location_Alan' and op2."PROPERTY_CODE" ='Quantity' 
and op."PROPERTY_CODE"='Tell_Quantity' and op."PROPERTY_VALUE" is null
and op3."PROJECT_STATUS" not in ('ProjectWF.Cancel','ProjectWF.Complete')

Error : SQL Error [42703]: ERROR: column "op" of relation "ORDER_PRODUCT" does not exist
Position: 48

Tags:

2

Answers


  1. 0

    I would ensure that you have a way to restore this table before trying any suggested update query. Ideally we would see some sample data in your question as I’m unsure if the suggestions below are valid.

    Please carefully consider the sample data I generated, see: https://dbfiddle.uk/xgucUoXX and also consider the results. This should help you refine the query before running on your data.

    Assuming you can use WITH then the simplest way to do this is via a common table expression:

    WITH cte
AS (
    SELECT
          op.id AS old_id
        , op2.PROPERTY_VALUE AS new_value
    FROM X.ORDER_PRODUCT op
    INNER JOIN X.ORDER_PRODUCT op2 ON op.SERVICE_ID = op2.SERVICE_ID
    INNER JOIN X.ORDER_PROJECT op3 ON op.ORDER_ID = op3.ID
    WHERE op2.PROPERTY_CODE = 'Quantity'
        AND op.PROPERTY_CODE = 'Tell_Quantity'
        AND op.PROPERTY_VALUE IS NULL
        AND op3.PROJECT_STATUS NOT IN ('ProjectWF.Cancel', 'ProjectWF.Complete')
        AND op.PRODUCT_CODE = 'Tell_Datacenter_Co-location_Alan'
    )
UPDATE X.ORDER_PRODUCT
SET PROPERTY_VALUE = cte.new_value
FROM cte
WHERE X.ORDER_PRODUCT.id = cte.old_id

    nb: As at v16, the update command does not directly support "join" ref. but does allow multiple table references through the from clause, and to then impose conditions through the where clause. So this may work without using WITH:

    UPDATE X.ORDER_PRODUCT AS op
SET PROPERTY_VALUE = op2.PROPERTY_VALUE
FROM X.ORDER_PRODUCT AS op2, X.ORDER_PROJECT AS op3
WHERE 
    op.SERVICE_ID = op2.SERVICE_ID
    AND op2.PROPERTY_CODE = 'Quantity'
    AND op.ORDER_ID = op3.ID
    AND op3.PROJECT_STATUS NOT IN ('ProjectWF.Cancel', 'ProjectWF.Complete')
    AND op.PROPERTY_VALUE IS NULL
    AND op.PROPERTY_CODE = 'Tell_Quantity'
    AND op.PRODUCT_CODE = 'Tell_Datacenter_Co-location_Alan'
    Login or Signup to reply.
  2. 0

    The error you’re getting is you are using the same table alias op for both the main table and the joined table. To fix this, you can use different table aliases for the main table and the joined table. For example, you can use op1 for the main table and op2 for the joined table.

    UPDATE "X"."ORDER_PRODUCT" op1
SET op1."PROPERTY_VALUE" = op2."PROPERTY_VALUE"
FROM "X"."ORDER_PRODUCT" op2
CROSS JOIN "X"."ORDER_PROJECT" op3
WHERE op1."SERVICE_ID" = op2."SERVICE_ID"
  AND op1."ORDER_ID" = op3."ID"
  AND op1."PRODUCT_CODE" = 'Tell_Datacenter_Co-location_Alan'
  AND op2."PROPERTY_CODE" = 'Quantity'
  AND op1."PROPERTY_CODE" = 'Tell_Quantity'
  AND op1."PROPERTY_VALUE" IS NULL
  AND op3."PROJECT_STATUS" NOT IN ('ProjectWF.Cancel', 'ProjectWF.Complete');

    In this updated query, I’ve used a different alias for the second instance of the "X"."ORDER_PRODUCT" table, which is necessary for the self-join to work correctly. The query should now update the "PROPERTY_VALUE" in the "X"."ORDER_PRODUCT" table as intended.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search