Gujjula Paramesh Gujjula Paramesh - 2 months ago 7
SQL Question

Update values of a specified column from one table to another

Query:

select * from emp;


Output:

ID NAME AGE SALARY ADDRESS
------ -------------------- ---------- ---------- --------------------
101 Naveen 28 25000 charminar
102 Vishal 32 28000 kothapet
180 Manish 25 18000 Begumpet
120 Sravan 29 20000 Vidyanagar
106 Mukesh 26 21000 Vanastalipuram
115 Sankar 23 15000 Kairtabad
130 Asif 24 16000 Sultan nagar
142 Deeraj 27 21500 Lalbazar


Query:

select * from dept;


Output:

JOB_ID JOB_TITLE HIRE_DATE EMP_ID
------ -------------------- --------- ----------
12254 Software Testing 16-APR-14
13696 Developer 25-MAR-13
13589 Manager 21-JUN-10
13696 developer 26-DEC-13
15395 DBA 15-APR-12
12254 Software testing 14-NOV-14
13696 Developer 17-FEB-11
15395 DBA 23-JAN-13


I have to copy all the (ID) values from table
EMP
into table
DEPT
(
EMP_ID
).

Answer

Like the other commenters mentioned, you would have to manually establish the link for each EMP_ID:

UPDATE dept SET EMP_ID = 101 WHERE JOB_ID = 12254 AND HIRE_DATE = TO_DATE('16-APR-14', 'DD-MON-YY');
UPDATE dept SET EMP_ID = 102 WHERE JOB_ID = 13696 AND HIRE_DATE = TO_DATE('25-MAR-13', 'DD-MON-YY');
UPDATE dept SET EMP_ID = 180 WHERE JOB_ID = 13589 AND HIRE_DATE = TO_DATE('21-JUN-10', 'DD-MON-YY');
UPDATE dept SET EMP_ID = 120 WHERE JOB_ID = 13696 AND HIRE_DATE = TO_DATE('26-DEC-13', 'DD-MON-YY');
UPDATE dept SET EMP_ID = 106 WHERE JOB_ID = 15395 AND HIRE_DATE = TO_DATE('15-APR-12', 'DD-MON-YY');
UPDATE dept SET EMP_ID = 115 WHERE JOB_ID = 12254 AND HIRE_DATE = TO_DATE('14-NOV-14', 'DD-MON-YY');
UPDATE dept SET EMP_ID = 130 WHERE JOB_ID = 13696 AND HIRE_DATE = TO_DATE('17-FEB-11', 'DD-MON-YY');
UPDATE dept SET EMP_ID = 142 WHERE JOB_ID = 15395 AND HIRE_DATE = TO_DATE('23-JAN-13', 'DD-MON-YY');

Just make sure EMP_ID in each statement is the desired one.

Comments