Update multiple records using subquery oracle

Question

I have two tables Table_A and Table_B Table_A has columns Id and description Table_B has columns Id, description and status. Now I have to update description column in Table_A for records with matching Id and a status value 'U' in Table_B. I used the following query

Update Test_A SET test_a.description = (SELECT test_b.description From Test_B Where Test_A.id = Test_B.id And Test_B.Status='U') Where EXISTS (SELECT id From Test_B WHERE test_a.id = test_b.id);

But this query not only updates the records with matching ID and status as 'U', but also updates the other rows in Table_A with description as null. I have some sample data prior to update and after update

Table_A

ID | Description |
1 | A |
2 | B |
3 | C |

Table_B

ID | Description | Status |
1 | AA |N |
3 | CC |U |

And after I run the query, I am getting the following results

Table_A

ID | Description |
1 | AA |
2 | B |
3 | (null) |

Since the flag for ID 3 is not 'U', it is not being selected from Table_B, but is being updated in Table_A. But I was expecting the Table_A record with Id 1 alone to be updated and all other records intact. Can someone please help me to identify where I go wrong?

Multisync · Accepted Answer · 2014-10-29 19:49:23Z

3

You need to add extra condition in your where clause:

Update Test_A 
   SET test_a.description 
          = (SELECT test_b.description From Test_B Where Test_A.id = Test_B.id And Test_B.Status='U') 
Where EXISTS (SELECT id From Test_B WHERE test_a.id = test_b.id And Test_B.Status='U');

If there are no NULL descriptions in TEST_B you may try this:

Update Test_A 
   SET test_a.description 
          = NVL((SELECT test_b.description From Test_B Where Test_A.id = Test_B.id And Test_B.Status='U'), test_a.description);

In this case all rows will be updated, but if the correlated subquery returns NULL the old value will remain

answered Oct 29, 2014 at 19:49

Multisync

8,8271 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nprak Over a year ago

That works. The second query was what I was looking for. Thanks

Collectives™ on Stack Overflow

Update multiple records using subquery oracle

Table_A

Table_B

Table_A

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table_A

Table_B

Table_A

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related