CODEWITHSUNDEEP
sqloracle-database
Wanda
Wanda

Reputation: 1

how to update all rows with matching column in same table in oracle

How can i update all rows (a column in all rows) against a specific fk_ID in a table where the matching column also exist in the same table against different fk_ID.

For Example I have a Table A it has column ID, Item, Status

fk_ID Item Status
10 AB Y
10 AS N
11 AX N
10 AD N
11 AB N
11 AD Y

I want to change the status of all rows with fk_ID = 11 as it is against fk_ID = 10

I have tried following so far

update table b set b.status = b.status where b.fk_ID  = '11' Self JOIN ON b.fk_ID = 10;

Upvotes: 0

Views: 193

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21063

Assuming the primary key of your table is ITEM and FK_ID and you want to pass the status from the FK_ID 10 to 11.

This query gives you the overview of the status

select t11.*, t10.status s_t10
from table_name t11
join table_name t10 on t11.item = t10.item
and t11.fk_id = 11 and t10.fk_id = 10;

     FK_ID IT S S_T10
---------- -- - -
        11 AB N Y
        11 AD Y N

So you expect two rows to be updated.

You may use this join query as an updateble join view as follows

update (        
select t11.*, t10.status status_t10
from table_name t11
join table_name t10 on t11.item = t10.item
and t11.fk_id = 11 and t10.fk_id = 10)
set status = status_t10;
2 rows updated.

Result

select * from table_name

     FK_ID IT S
---------- -- -
        10 AB Y
        10 AS N
        11 AX N
        10 AD N
        11 AB Y  <<--- update
        11 AD N  <<--- update

Note that the above assumption about the PK must be valid (or at least a unique index must be defined, as otherwise Oracle fails with ORA-01779: cannot modify a column which maps to a non key-preserved table.

This is meaningfull, as if you have more rows with the same item in the FK_ID = 10 - what should you use for the update??

Upvotes: 0

MT0
MT0

Reputation: 167972

If you want to update only the partial set of rows for fk_id of 10 where there is a matching item for a fk_id of 11 then you can use a MERGE statement:

MERGE INTO table_name dst
USING (
  SELECT item, status
  FROM   table_name
  WHERE  fk_id = 11
) src
ON (dst.fk_id = 10 AND src.item = dst.item)
WHEN MATCHED THEN
  UPDATE SET status = src.status;

Which, for your sample data:

CREATE TABLE table_name (fk_ID, Item, Status) AS
SELECT 10, 'AB', 'Y' FROM DUAL UNION ALL
SELECT 10, 'AS', 'N' FROM DUAL UNION ALL
SELECT 11, 'AX', 'N' FROM DUAL UNION ALL
SELECT 10, 'AD', 'N' FROM DUAL UNION ALL
SELECT 11, 'AB', 'N' FROM DUAL UNION ALL
SELECT 11, 'AD', 'Y' FROM DUAL;

After the merge then:

SELECT *
FROM   table_name;

Outputs:

FK_ID ITEM STATUS
10 AB N
10 AS N
11 AX N
10 AD Y
11 AB N
11 AD Y

If you want to change all the values (to remove the items that do not exist for then fk_id of 11 and then insert/update those that are new/modified) you need two statements:

DELETE FROM table_name
WHERE fk_id = 10
AND   item NOT IN (SELECT item FROM table_name WHERE fk_id = 11);

MERGE INTO table_name dst
USING (
  SELECT item, status
  FROM   table_name
  WHERE  fk_id = 11
) src
ON (dst.fk_id = 10 AND src.item = dst.item)
WHEN MATCHED THEN
  UPDATE SET status = src.status
WHEN NOT MATCHED THEN
  INSERT (fk_id, item, status) VALUES (10, src.item, src.status);

Then:

SELECT *
FROM   table_name;

Outputs:

FK_ID ITEM STATUS
10 AB N
11 AX N
10 AD Y
11 AB N
11 AD Y
10 AX N

db<>fiddle here

Upvotes: 1

Related Questions