CODEWITHSUNDEEP
sqloracle-database
gene b.
gene b.

Reputation: 11984

Update Column with Value Existing for Another Row

My table, TEST_T, in Oracle:

COMPONENT_ID | FORM_ID | COMPLETE_FLAG
--------------------------------------
30101          9         N
30101          11        Y
30101          23        Y
--------------------------------------
30162          9         N
30162          11        N
30162          23        N

I need to update all COMPLETE_FLAG entries for COMPONENT_ID = 30162 to be the same as for 30101 per each corresponding FORM_ID.

Is there a simple UPDATE query that can do this?

Upvotes: 0

Views: 70

Answers (2)

user2941651
user2941651

Reputation:

My first answer:

UPDATE
    TEST_T
SET
    TEST_T.COMPLETE_FLAG = A.COMPLETE_FLAG
FROM
    TEST_T 
    INNER JOIN TEST_T AS A
        ON TEST_T.FORM_ID = A.FORM_ID 
           AND TEST_T.COMPONENT_ID = 30162 
           AND A.COMPONENT_ID = 30101

was not correct for Oracle (it was written mistakenly for MSSQL)

Translating it for Oracle it could be:

UPDATE
    TEST_T
SET
    TEST_T.COMPLETE_FLAG = (SELECT A.COMPLETE_FLAG
       FROM
          TEST_T A
       WHERE 
          TEST_T.FORM_ID = A.FORM_ID 
          AND A.COMPONENT_ID = 30101)
WHERE
    TEST_T.COMPONENT_ID = 30162;

Upvotes: 3

Chris Hep
Chris Hep

Reputation: 1141

This is as simple an update as I can think that will work.

UPDATE test_t t
   SET t.complete_flag =
       (SELECT tt.complete_flag
          FROM test_t tt
         WHERE tt.component_id = 30101
           AND tt.form_id = t.form_id)
 WHERE t.component_id = 30162;

Upvotes: 3

Related Questions