Home » SQL & PL/SQL » SQL & PL/SQL » Data cleansing with Pl/SQL update
Data cleansing with Pl/SQL update [message #4855] Fri, 17 January 2003 07:38 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Mangesh,

Given
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
If I do
SQL> 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
Previous Topic: How to drop the table whose name is in lower case?
Next Topic: Problem when displaying sysdate in pl/sql block
Goto Forum:
  


Current Time: Tue Jul 22 05:10:37 CDT 2025