Question

How do I set a column attribute with value from another table?

I have this manager_specialisation table:

CREATE TABLE manager_specialisation (
specialisation_id NUMBER(4) NOT NULL,
specialisation_type VARCHAR(50) NOT NULL
);

And I want to update a column in my garage_manager table like this except the specialisation_id is WHERE specialisation_type = 'S'.?

UPDATE garage_manager
SET specialisation_id=manager_specialisation.specialisation_id
WHERE man_id=1 AND garage_email= (SELECT garage_code
FROM garage
WHERE garage_email = 'melbournec@rdbms.example.com') ;

Answer 1

As you want copy a column from on one table to another table the below query will update the garage_manager table with specialisation_id where specialisation_type='S' by using this nested query.

The nested query selects the specialisation_id from manager_specialisation with the condition specialisation_type='S'.

The table garage_manager is taken as gm as object.

Hope you understood.

QUERY:

UPDATE garage_manager gm

SET specialisation_id=manager_specialisation.specialisation_id

WHERE gm.specialisation_id=(SELECT specialisation_id form manager_specialisation WHERE  specialisation_type='S' );