Updating a column in one table through a column in another table

Question

I have 2 tables 'A' and 'B'. Both have a common column 'name' and linked with each other by a column 'id'. The column 'name' in the table A is empty whereas it has data in table B. My task is to fill all the data in that column from table B to table A with corresponding ids.

I'm using the following query:

UPDATE A
SET A.name = (SELECT B.name from B WHERE A.id = B.id)
WHERE EXISTS
(SELECT B.name from B)

As soon as I run the query in SQL developer, it just freezes and i have to force close it. Can anyone tell me what's wrong with the query?

I think that your intention is to update name by corresponding name from B, but your exists does not represent this intention - it is true for each record in A if there is at least one row in B. — Nikola Markovinović, Sep 04 '12 at 16:12
You need to add `WHERE a.id = b.id` to the `WHERE EXISTS` clause, because it's really checking 'are there rows in table `b`, not 'is there a row for this id in table `b`. And don't select a column, select a constant. If the two id columns are in an index, but the `name` column isn't (or is in a different index), that would mean the query would only need to access the index, not the table too. — Clockwork-Muse, Sep 04 '12 at 16:14
You need to add `WHERE A.id = B.id` to exists() to correlate update candidate to actual data. — Nikola Markovinović, Sep 05 '12 at 08:06
@Nikola, X-Zero: Thanks guys. I see the flaw in my query. It was freezing because I had too many rows and no index. I just did the indexing and it is working fine now. — Snow Leopard, Sep 05 '12 at 08:34

Leniel Maccaferri · Answer 1 · 2012-09-04T16:29:39.643

6

Try this one instead:

UPDATE A
SET A.name = (SELECT B.name FROM B WHERE B.id = A.id AND B.name IS NOT NULL)
WHERE a.name IS NULL;

Since you're using Oracle, here's the reference for IS NOT NULL.

Here's the SQL Fiddle so that you can play with it: http://sqlfiddle.com/#!4/a5ad0/3

edited Sep 04 '12 at 16:29

answered Sep 04 '12 at 16:06

Leniel Maccaferri

100,159
46
371
480

Thanks for the reply. I tried the query but it's not working either. It can't be a problem of server right since other queries are working fine? – Snow Leopard Sep 05 '12 at 06:57
It was freezing because I had too many rows and no index. I just did the indexing and it is working fine now. – Snow Leopard Sep 05 '12 at 08:35

score 2 · Answer 2 · answered Sep 07 '12 at 12:55

I'm not sure from the above conversation whether you made any changes beyond indexing your data, but you should include a WHERE EXISTS clause as mentioned. The complete query should look like this:

UPDATE A
   SET A.name = ( SELECT B.name FROM B WHERE B.id = A.id )
 WHERE EXISTS ( SELECT 1 FROM B WHERE B.id = A.id )

The WHERE EXISTS clause in your original query won't do much of anything except check to see if there is at least one non-NULL value of name in B.

Updating a column in one table through a column in another table

2 Answers2

Linked