Home » SQL & PL/SQL » SQL & PL/SQL » Compare 2 tables and upadte 1 (pl sql)
Compare 2 tables and upadte 1 [message #651361] |
Mon, 16 May 2016 09:21 |
|
Helen_86
Messages: 5 Registered: May 2016
|
Junior Member |
|
|
Hello, I am newbie with pl sql.
I want to do this:
TABLE A
ID-Name-Date
1-Ralf-20160425
2-Marge-
3-Homer-20150115
4- - 20140212
TABLE B
ID-Name-Date
2-Marge-20160505
3-Homer-20150115
4-Bart - 20140212
55-Maggie-20160715
TABLE OUTPUT
1-Ralf-20160425
2-Marge-20160505
3-Homer-20150115
4-Bart- 20140212
55-Maggie-20160715
I need to compare Table A with Table B and update Table A with new registers and differente fields of table B. I wnat to wok with cursors.
How can i do that? thank you!!
[Updated on: Mon, 16 May 2016 09:23] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Compare 2 tables and upadte 1 [message #651403 is a reply to message #651361] |
Tue, 17 May 2016 08:04 |
|
Since you have not provided any working test cases or statements , let me guess there is two tables EMP and DEPT and i want to update one by overlooking on the values to another table.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
update records in one table based on values in another table.
UPDATE emp
SET ename = ( SELECT dname
FROM dept
WHERE emp.deptno = dept.deptno)
WHERE EXISTS
( SELECT dname
FROM dept
WHERE emp.deptno = dept.deptno);
I don't know if this is what you wanted. Post back with the details seniors asked anyway.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 12:49:23 CDT 2024
|