Home » SQL & PL/SQL » SQL & PL/SQL » Updating duplicate records with different data (Oracle 11g)
| Updating duplicate records with different data [message #647545] |
Mon, 01 February 2016 22:47  |
 |
bhush5bhai
Messages: 1
Registered: February 2016
|
Junior Member |
|
|
Hi all,
I have a migration specific issue. Data from a large table has been migrated into another table, but data for one destination column is missing due to certain issues (that are not specific to this thread).
Currently I am trying to update the data for missing column in the new table.
The problem is because of the current data in the newtable, it has duplicate rows and I haven't found a way to do one to one mapping of rows so that data for the missing column can be updated.
create table oldtable
(
fileNo varchar(10),
folder varchar(10),
fileType varchar(10),
col_1 varchar(10),
col_2 varchar(10),
col_3 varchar(10),
col_4 varchar(10)
);
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('ABC' , 'Folder1','Type1' ,'111' ,'111','111' ,null);
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('ABC' , 'Folder1','Type2' ,'111' ,'111','111',null);
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('GHI' , 'Folder3','Type3' ,'333' ,'333','333','333');
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('JKL' , 'Folder4','Type3' ,'444' ,'444','444','444');
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('MNO' , 'Folder5','Type4' ,'555' ,'555' ,'555' ,'555');
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('PQR' , 'Folder6','Type4' ,'666' ,'666' ,'666' ,'666');
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('STU' , 'Folder7','Type5' ,'777' ,'777','777','777')
create table newtable
(
fileNo varchar(10),
folder varchar(10),
fileType varchar(10),
col_1 varchar(10),
col_2 varchar(10),
col_3 varchar(10),
col_4 varchar(10)
);
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('ABC' , 'Folder1', null ,'111' ,'111','111' ,null);
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('ABC' , 'Folder1', null,'111' ,'111','111',null);
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('GHI' , 'Folder3', null ,'333' ,'333','333','333');
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('JKL' , 'Folder4',null ,'444' ,'444','444','444');
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('MNO' , 'Folder5',null ,'555' ,'555' ,'555' ,'555');
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('PQR' , 'Folder6',null ,'666' ,'666' ,'666' ,'666');
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('STU' , 'Folder7',null ,'777' ,'777','777','777')
select * from oldtable;
FILENO FOLDER FILETYPE COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC Folder1 Type1 111 111 111
ABC Folder1 Type2 111 111 111
GHI Folder3 Type3 333 333 333 333
JKL Folder4 Type3 444 444 444 444
MNO Folder5 Type4 555 555 555 555
nPQR Folder6 Type4 666 666 666 666
STU Folder7 Type5 777 777 777 777
select * from newtable;
FILENO FOLDER FILETYPE COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC Folder1 111 111 111
ABC Folder1 111 111 111
GHI Folder3 333 333 333 333
JKL Folder4 444 444 444 444
MNO Folder5 555 555 555 555
PQR Folder6 666 666 666 666
STU Folder7 777 777 777 777
When trying to update FileType column for all the records -
update newtable set FileType = (select FileType from oldtable where Fileno = newtable.fileNo and folder = newtable.Folder);
It would work fine for all other records except first two which are currently duplicate.
It won't obviously work because it would give - 'single row subquery returns multiple rows' error because of those duplicate records.
I am trying to update FileType column for first two rows with values "Type1" and "Type2".
Out of those two records, it won't matter which record from newtable is updated with "Type1" and which one gets "Type2".
Please help with any ideas around updating such duplicate records with the specific data from the oldtable into newtable.
[Updated on: Mon, 01 February 2016 22:52] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Updating duplicate records with different data [message #647559 is a reply to message #647557] |
Tue, 02 February 2016 03:55  |
 |
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
|
Junior Member |
|
|
try the following PLSQL Code to get the job done.
DECLARE
CURSOR C1
IS
SELECT ROWID AS ID, FILENO, FOLDER
FROM
NEWTABLE
WHERE
FILETYPE IS NULL ;
REC_C1 C1%ROWTYPE;
LV_FILETYPE NEWTABLE.FILETYPE%TYPE;
BEGIN
FOR REC_C1 IN C1
LOOP
LV_FILETYPE := NULL;
SELECT FILETYPE
INTO
LV_FILETYPE
FROM
OLDTABLE
WHERE
FILENO = REC_C1.FILENO
AND
FOLDER=REC_C1.FOLDER
AND FILETYPE NOT IN (
SELECT FILETYPE
FROM
NEWTABLE
WHERE
FILENO = REC_C1.FILENO
AND
FOLDER=REC_C1.FOLDER
AND
FILETYPE IS NOT NULL)
AND
ROWNUM = 1;
UPDATE NEWTABLE NT
SET
FILETYPE = LV_FILETYPE
WHERE
ROWID = REC_C1.ID;
COMMIT;
END LOOP;
END;
[Edit MC: format code]
[Updated on: Tue, 02 February 2016 04:06] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Updating duplicate records with different data [message #647569 is a reply to message #647545] |
Tue, 02 February 2016 10:27 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SQL> update newtable
2 set FileType = (
3 select FileType
4 from oldtable
5 where oldtable.Fileno = newtable.fileNo
6 and oldtable.folder = newtable.Folder
7 and rownum = 1
8 )
9 where (Fileno,folder) in (
10 select Fileno,
11 folder
12 from oldtable
13 )
14 /
7 rows updated.
SQL> select *
2 from newtable
3 /
FILENO FOLDER FILETYPE COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC Folder1 Type1 111 111 111
ABC Folder1 Type1 111 111 111
GHI Folder3 Type3 333 333 333 333
JKL Folder4 Type3 444 444 444 444
MNO Folder5 Type4 555 555 555 555
PQR Folder6 Type4 666 666 666 666
STU Folder7 Type5 777 777 777 777
7 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: Updating duplicate records with different data [message #647571 is a reply to message #647570] |
Tue, 02 February 2016 11:07 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Maybe like that:
SQL> select * from oldtable order by fileno, folder;
FILENO FOLDER FILETYPE COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC Folder1 Type1 111 111 111
ABC Folder1 Type2 111 111 111
GHI Folder3 Type3 333 333 333 333
JKL Folder4 Type3 444 444 444 444
MNO Folder5 Type4 555 555 555 555
PQR Folder6 Type4 666 666 666 666
STU Folder7 Type5 777 777 777 777
7 rows selected.
SQL> select * from newtable order by fileno, folder;
FILENO FOLDER FILETYPE COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC Folder1 111 111 111
ABC Folder1 111 111 111
GHI Folder3 333 333 333 333
JKL Folder4 444 444 444 444
MNO Folder5 555 555 555 555
PQR Folder6 666 666 666 666
STU Folder7 777 777 777 777
7 rows selected.
SQL> update newtable
2 set FileType = (select o.FileType
3 from (select o.*,
4 row_number()
5 over (partition by Fileno, fileNo order by null) rn
6 from oldtable o) o,
7 (select n.*, rowid rid,
8 row_number()
9 over (partition by Fileno, fileNo order by null) rn
10 from newtable n ) n
11 where o.Fileno = newtable.fileNo
12 and o.folder = newtable.Folder
13 and o.Fileno = n.Fileno
14 and o.folder = n.folder
15 and n.rid = newtable.rowid
16 and o.rn = n.rn)
17 where (Fileno,folder) in (select Fileno,folder from oldtable)
18 /
7 rows updated.
SQL> select * from newtable order by fileno, folder;
FILENO FOLDER FILETYPE COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC Folder1 Type1 111 111 111
ABC Folder1 Type2 111 111 111
GHI Folder3 Type3 333 333 333 333
JKL Folder4 Type3 444 444 444 444
MNO Folder5 Type4 555 555 555 555
PQR Folder6 Type4 666 666 666 666
STU Folder7 Type5 777 777 777 777
7 rows selected.
[Updated on: Tue, 02 February 2016 11:09] Report message to a moderator |
|
|
|
| Re: Updating duplicate records with different data [message #647572 is a reply to message #647570] |
Tue, 02 February 2016 11:11 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I see, you want not just anyone FILETYPE but each of tehm. Then use MERGE + analytic ROW_NUMBER:
SQL> merge
2 into newtable d
3 using (
4 with t1 as (
5 select rowid rid,
6 Fileno,
7 folder,
8 row_number() over(partition by Fileno,folder order by FileType) rn
9 from newtable
10 ),
11 t2 as (
12 select Fileno,
13 folder,
14 FileType,
15 row_number() over(partition by Fileno,folder order by FileType) rn
16 from oldtable
17 )
18 select t1.rid,
19 t2.FileType
20 from t1,
21 t2
22 where t1.Fileno = t2.Fileno
23 and t1.folder = t2.folder
24 and t1.rn = t2.rn
25 ) s
26 on (
27 d.rowid = s.rid
28 )
29 when matched
30 then update
31 set d.FileType = s.FileType
32 /
7 rows merged.
SQL> select *
2 from newtable
3 /
FILENO FOLDER FILETYPE COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC Folder1 Type1 111 111 111
ABC Folder1 Type2 111 111 111
GHI Folder3 Type3 333 333 333 333
JKL Folder4 Type3 444 444 444 444
MNO Folder5 Type4 555 555 555 555
PQR Folder6 Type4 666 666 666 666
STU Folder7 Type5 777 777 777 777
7 rows selected.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 21:52:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
