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

sql oracle oracle10g sql-update tablecolumn
Snow Leopard picture Snow Leopard · Sep 4, 2012 · Viewed 10.3k times · Source

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?

Answer

Leniel Maccaferri picture Leniel Maccaferri · Sep 4, 2012

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

Related questions

CASE .. WHEN expression in Oracle SQL

I have the table with 1 column and has following data Status a1 i t a2 a3 I want to display the following result in my select query Status| STATUSTEXT a1 | Active i | Inactive t | Terminated a2 | Active a3 | Active One …

Read More
How to insert date values into table

How can I insert into table with different input using / ,with date datatype? insert into run(id,name,dob)values(&id,'&name',[what should I write here?]); I'm using oracle 10g.

Read More
ORA-01861: literal does not match format string

When I try to execute this snippet: cmd.CommandText = "SELECT alarm_id,definition_description,element_id, TO_CHAR (alarm_datetime, 'YYYY-MM-DD HH24:MI:SS'),severity, problem_text,status FROM aircom.alarms WHERE status = 1 and TO_DATE (alarm_datetime,'DD.MM.…

Read More