Data cleansing with Pl/SQL update [message #4855] |
Fri, 17 January 2003 07:38  |
manmesh
Messages: 4 Registered: January 2003
|
Junior Member |
|
|
Hello All Guru,
I am doing data cleansing for a schema
One table has 2 fields
1. generic_code
2. description
For that I need to do the following
I need to update all the values of second field i.e. "description" with the value of the 1st description in "generic_code" group , and if the value in "description" is very 1st entry for the generic_code then want to keep it same ,but further entries will have same values as the 1st one, e.g.
generic_code--------------------------------description
L36880-N4050-@181------------------------MP/C35i/Dutchtone/
L36880-N4050-@151------------------------MP/S35i/Ger./polar
L36880-N4050-@181------------------------MP3-Player USB
L36880-N4050-@151------------------------K45 Leather Case
L36880-N4050-@152------------------------MP/S35i/E-Plus/pol
In this example "generic_code" L36880-N4050-@181 has two different "description" "MP/C35i/Dutchtone" and "MP3-Player USB"
as "description"- "MP3-Player USB" is a second entry for "generic_code"- L36880-N4050-@181 I want to replace/update it with "MP/C35i/Dutchtone/"( with 1st entry ), same is true for other generic_code "L36880-N4050-@151"
I need to write sql script/ procedure for this ( need to use a cursor ?) , Your help would be appreciated
TIA
Mangesh
|
|
|
Re: Data cleansing with PL/SQL update [message #4862 is a reply to message #4855] |
Fri, 17 January 2003 08:39   |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Mangesh,
The attached code will work, but:- You say you want to update a generic_code with its "1st" description, but how are you defining "1st"? The way I have the code set up in this example, I'm updating each generic_code with its descriptino that appears first alphabetically. This distinction seems somewhat arbitrary, so you must be sure that it meets your specific business requirement.
- After the UPDATE statement runs, you will have rows in your table that will have duplicated combinations of generic_code and description, will this be OK?
Here is what I did:SQL> CREATE TABLE t1 (generic_code VARCHAR2(30), description VARCHAR2(100));
Table created.
SQL> INSERT INTO t1 VALUES ('L36880-N4050-@181','MP/C35i/Dutchtone/');
1 row created.
SQL> INSERT INTO t1 VALUES ('L36880-N4050-@151','MP/S35i/Ger./polar');
1 row created.
SQL> INSERT INTO t1 VALUES ('L36880-N4050-@181','MP3-Player USB');
1 row created.
SQL> INSERT INTO t1 VALUES ('L36880-N4050-@151','K45 Leather Case');
1 row created.
SQL> INSERT INTO t1 VALUES ('L36880-N4050-@152','MP/S35i/E-Plus/pol');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t1;
GENERIC_CODE DESCRIPTION
------------------------------ --------------------------------------------------------
L36880-N4050-@181 MP/C35i/Dutchtone/
L36880-N4050-@151 MP/S35i/Ger./polar
L36880-N4050-@181 MP3-Player USB
L36880-N4050-@151 K45 Leather Case
L36880-N4050-@152 MP/S35i/E-Plus/pol
SQL> UPDATE t1 t1 SET description = (SELECT MIN(t2.description)
2 FROM t1 t2
3 WHERE t2.generic_code = t1.generic_code
4 GROUP BY t2.generic_code)
5 /
5 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t1;
GENERIC_CODE DESCRIPTION
------------------------------ --------------------------------------------------------
L36880-N4050-@181 MP/C35i/Dutchtone/
L36880-N4050-@151 K45 Leather Case
L36880-N4050-@181 MP/C35i/Dutchtone/
L36880-N4050-@151 K45 Leather Case
L36880-N4050-@152 MP/S35i/E-Plus/pol
SQL> Good luck, Mangesh.
A
|
|
|
Re: Data cleansing with PL/SQL update [message #4866 is a reply to message #4862] |
Fri, 17 January 2003 09:33   |
mangesh
Messages: 28 Registered: May 2002
|
Junior Member |
|
|
Hi Art ,
I am very sorry for such a bad formation of my Question, that is way u just misintepreted my question
Actaullay I can Explan it with following two tables ...
Table Before data cleansing .....
generic_code--------------------------------description
L36880-N4050-@151------------------------MP/S35i/Ger./polar
L36880-N4050-@151------------------------K45 Leather Case
L36880-N4050-@152------------------------MP/S35i/E-Plus/pol
L36880-N4050-@181------------------------MP/C35i/Dutchtone/
L36880-N4050-@181------------------------MP3-Player USB
Table after Data Cleansing .......
generic_code--------------------------------description
L36880-N4050-@151------------------------MP/S35i/Ger./polar
L36880-N4050-@151------------------------MP/S35i/Ger./polar <----changed data
L36880-N4050-@152------------------------MP/S35i/E-Plus/pol
L36880-N4050-@181------------------------MP/C35i/Dutchtone/
L36880-N4050-@181------------------------MP/C35i/Dutchtone/ <----changed dataa
so "description" is the column which gets updated and description -MP3-Player USB gets changed with MP/C35i/Dutchtone because both have same generic_code(L36880-N4050-@181) and so the second description for generic_code L36880-N4050-@181 i.e.
MP3-Player USB got changed to MP/C35i/Dutchtone ( which is the 1st value of description for L36880-N4050-@181-generic_code sorrted the records with order by generic_code in ascending order )
|
|
|
Re: Data cleansing with PL/SQL update [message #4868 is a reply to message #4862] |
Fri, 17 January 2003 10:16  |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Mangesh,
GivenSQL> SELECT * FROM t1;
GENERIC_CODE DESCRIPTION
------------------------------ -------------------
L36880-N4050-@181 MP/C35i/Dutchtone/
L36880-N4050-@151 MP/S35i/Ger./polar
L36880-N4050-@181 MP3-Player USB
L36880-N4050-@151 K45 Leather Case
L36880-N4050-@152 MP/S35i/E-Plus/pol If I doSQL> SELECT generic_code
3 , description
4 FROM t1
5 WHERE generic_code = 'L36880-N4050-@151'
6 ORDER BY generic_code
7 / which of the following scenarios will be the outcome, and why?GENERIC_CODE DESCRIPTION
------------------------------ ----------------------
L36880-N4050-@151 K45 Leather Case
L36880-N4050-@151 MP/S35i/Ger./polar
GENERIC_CODE DESCRIPTION
------------------------------ ----------------------
L36880-N4050-@151 MP/S35i/Ger./polar
L36880-N4050-@151 K45 Leather Case A
|
|
|