Dear Experts
I want to insert record in city table by matching with AGENT_TEMP and take the state_code from state table.
when no record found for the respective cities in city table then I need to insert else no insert/update is required.
CREATE TABLE AGENT_TEMP
(
NAME VARCHAR2(200),
STATE VARCHAR2(20),
CITY VARCHAR2(20)
);
INSERT INTO AGENT_TEMP VALUES ('JAMES','ALASKA','ANCHORAGE');
INSERT INTO AGENT_TEMP VALUES ('JACOB','ARIZONA','PHOENIX');
INSERT INTO AGENT_TEMP VALUES ('JOHN','ARKANSAS','LITTLE ROCK');
CREATE TABLE STATE
(
STATE_NAME VARCHAR2(20),
STATE_CODE VARCHAR2(20)
);
INSERT INTO STATE VALUES('ALASKA','21');
INSERT INTO STATE VALUES('ARIZONA','22');
INSERT INTO STATE VALUES('ARKANSAS','23');
CREATE TABLE CITY
(
CITY_ID NUMBER,
CITY_NAME VARCHAR2(20),
STATE_ID VARCHAR2(20)
);
CREATE SEQUENCE CITY_SEQ START WITH 101 INCREMENT BY 1;
INSERT INTO CITY VALUES(101,'LITTLE ROCK',23);
commit;
Now in city table i am having only one record for LITTLE ROCK i want to insert other two records which is not configured.
Expected result
Please help