CODEWITHSUNDEEP
sqloracle10g
P. Hoang
P. Hoang

Reputation: 461

SQL Update column with data from another table

I have 2 tables below

DEVICES
--------------------
DEVICE_ID   MODEL_ID
1           A
2           B
3           C
4           D


CHANGE_VALUES
----------------------------------------
D_ID    CHANGE_MODEL_ID
1           E
4           F

Now I'd like to update the value of MODEL_ID in DEVICES table for all the DEVICE_ID having values listed in D_ID in table CHANGE_VALUES.

The SQL query I used to achieve the objective is shown below. However, it does not seem to be a good solution since for every value of DEVICE_ID , it is required to perform 2 sub-queries. Running this on CHANGE_VALUES containing ~ 3k rows took me 34 seconds, which seems to be long. 

UPDATE devices dev
SET dev.model_id =
   (SELECT c_value.change_model_id
    FROM change_values c_value
    WHERE dev.device_id = c_value.d_id
   )
WHERE 1        IN
  (SELECT COUNT(*)
  FROM change_values c_value
  WHERE dev.device_id = c_value.d_id
  );

As I'm new to SQL, I don't know if there is any existing ways that can do the same thing. So I'm curious to know if is there any better solutions.

Note: I'm using Oracle 10g

Upvotes: 3

Views: 10964

Answers (2)

Aspirant
Aspirant

Reputation: 2278

update devices a set model_id=(select change_model_id from change_values b where b.d_id=a.device_id) 
where device_id in (select d_id from change_values)

Upvotes: 0

Clockwork-Muse
Clockwork-Muse

Reputation: 13046

Your biggest problem is that you're issuing a COUNT(*) for each row in devices - that's not what you actually need to do; you'll want an EXISTS clause instead. The following should work for any RDBMS:

UPDATE Devices d SET model_id = (SELECT c.change_model_id
                                 FROM Change_Values c
                                 WHERE c.d_id = d.device_id)
WHERE EXISTS (SELECT *
              FROM Change_Values c
              WHERE c.d_id = d.device_id);

Obviously, this is presuming you have an index on Devices.device_id. One on Change_Values.d_id may also speed things up.

Upvotes: 3

Related Questions