Oracle update with a left join

user_801523 0
Viewed 248

I want to simply update a single table set of rows with parameters passed in and not from another table but to identify the rows, I need to do a left join. Here is the query to select:

SELECT *
FROM SAMPLE_STATUS pss
  LEFT JOIN QC q ON q.SAMPLE_ID = pss.SAMPLE_ID
WHERE q.CONTRACT_CLN_ID = 28 AND q.LOT = 1

I have tried several examples here is the last one with just one column

UPDATE 
  (SELECT pss.APP_WIN_START_DT, TO_CHAR(sysdate, 'YYYYMMDD') AS NEW_AWSD
  FROM SAMPLE_STATUS pss
    LEFT JOIN QC q ON q.SAMPLE_ID = pss.SAMPLE_ID
  WHERE q.CONTRACT_CLN_ID = 28 AND q.LOT = 1) pq
  SET APP_WIN_START_DT = NEW_AWSD

Several records are pulled from the QC table that join to the Sample_Status table via the sample id. The error I get is :

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

Thanks in advance.

left-join sql-update oracle sql
1 Answers

I think that you actually want inner join logic here, but in any case you may write this update using exists:

UPDATE SAMPLE_STATUS pss
SET APP_WIN_START_DT = TO_CHAR(sysdate, 'YYYYMMDD')
WHERE EXISTS (SELECT 1 FROM QC q
              WHERE q.SAMPLE_ID = pss.SAMPLE_ID AND
                    q.CONTRACT_CLN_ID = 28 AND q.LOT = 1);

The reason I suspect you don't want a left join here is that you are updating a field of the table on the left side of the join. But since you have restrictions in the where clause on the right side table, it would just behave like an inner join anyway.

user_18632290
Related