how to save old value with new value in oracle or sql procedure
1. User Table 2. User_Hist Table
a> 1st table i have fileds like user Id , city
b> 2nd table ID, User Id, city ,city_old
------------------------------------------
user Id city
101 Andhra-Pradesh
------------------------------------------
2nd table i want like
-----------------------------------------------------------------
ID User Id City City_Old
-----------------------------------------------------------------
1 101 MP Andhra-Pradesh
1 101 UP MP
1 101 Kernataka UP
BUT IF I CHANGE CITY ON 2ND TIME SO I GOT THE RECORD ON TABLE LIKE
---------------------------------------------------------------------------
ID User Id City City_Old
--------------------------------------------------------------------------------
1 101 MP Andhra-Pradesh
1 101 MP Andhra-Pradesh
1 101 MP Andhra-Pradesh
and my procedure is
create or replace PROCEDURE PROC_UPDATE_CITY
(
P_CITY VARCHAR2,
P_USER_ID VARCHAR2,
P_RECORDS OUT NUMBER
)
AS
BEGIN
COMMIT;
P_RECORDS:= 0;
INSERT INTO USER_HIST(USER_ID,CITY)
select USER_ID,CITY from USER where USER_ID = P_USER_ID ;
UPDATE USER
SET CITY = P_CITY
WHERE USER_ID = P_USER_ID ;
UPDATE USER_HIST
SET CITY = P_CITY CITY_OLD = CITY where USER_ID = P_USER_ID ;
P_RECORDS:= 1;
END PROC_UPDATE_CITY;
SO HOW TO GET RECORD LIKE
-----------------------------------------------------------------
ID User Id City City_Old
-----------------------------------------------------------------
1 101 MP Andhra-Pradesh
1 101 UP MP
1 101 Kernataka UP
AND WHERE I DID MISTAKE