473,287 Members | 1,927 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Deleting repeated data

The table contains data as follows,

SAP_CUS TOMER_NBR SAP_CUSTOMER_NBR1
A B
B A
C D
D C
C E
E C


A=B is same as B=A

The final table should be as follows,

SAP_CUSTOMER_NBR SAP_CUSTOMER_NBR1
A B
C D
C E

please help me in doing this
Aug 23 '07 #1
3 1575
Jim Doherty
897 Expert 512MB
The table contains data as follows,

SAP_CUS TOMER_NBR SAP_CUSTOMER_NBR1
A B
B A
C D
D C
C E
E C


A=B is same as B=A

The final table should be as follows,

SAP_CUSTOMER_NBR SAP_CUSTOMER_NBR1
A B
C D
C E

please help me in doing this

Provided the duplication set is as you say a single character swapped round like you indicate, the principle is to extract the SAME AS records as you point out and remove them. The logic being to identify the row to extract and leave you with the result set you want. So.. how can this be done?.

Each character will have an ASCII representation, a numeric value. 'A'=65 and B='66' Added together they make 131 so over two rows we are going to see two values of 131.

If we created a third column to store the ASCII value and worked on that we could extract out the MAXIMUM record ID identifying the row, extract those rows into a temporary table compare the tables against each other and remove the rows by the relevant SQL comparison on a LEFT INNER JOIN between the relevant tables.

One word of CAUTION here, The ascii values when summed will give a value remember that 8+5=12 so does 5+7 so the actual theory has limited scope
and is absolutely based on your data provided above, You do NOT want a summation giving you an erronous result value on which you base your delete.

Anyway below is a script I knocked up to assist you in demonstrating the HOWS!! not intended as the SOLUTION obviously as that is for you to deal with If you create a physical table ..like so

CREATE TABLE mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )

and populate it with data of the type you provided above and then run the script below in query analyser you will see that it works only on temporary tables, which you can adjust to suit your production environment when you are ready and happy it works for you.

--USE whateverdatabasenamehere --<<<replace database name with yours
SET NOCOUNT ON
--create a temporary table to store the values
CREATE TABLE #mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )
--and the insert into this table
INSERT #mytable
--values from the main production table
SELECT * from mytable --<<substitute your table name here the column orders must match
--then update the temporary table with --the ACSII numeric values representing the characters ie A and B summed together=131
UPDATE #mytable
SET asciivalue=ASCII(sap_customer_nbr) + ASCII(sap_customer_nbr1)
--create another temporary table
CREATE TABLE #mylink (
[asciivalue] [int] null ,
[recid] [int] null )
--into which we will insert
INSERT #mylink
-- the MAXIMUM RecordID (RecID) for each Asciivalue which essential only gives
-- ACTUAL recordID to identify the row that we wish ultimately to Delete from the table
SELECT #mytable.asciivalue, MAX(#mytable.recid) AS linkkey
FROM #mytable
GROUP BY #mytable.asciivalue

--the next two lines are left in to merely show you in query analyser the resultsets
SELECT * FROM #mytable
SELECT * FROM #mylink

--we now delete from the first temporary table the rows we wish to remove
DELETE #mytable
FROM #mylink INNER JOIN #mytable ON #mylink.recid = #mytable.recid
--and to finalise we show the results again in query analyser
SELECT * FROM #mytable
--and finally drop the temporary explicitly
DROP TABLE #mylink
DROP TABLE #mytable
Aug 23 '07 #2
ck9663
2,878 Expert 2GB
Provided the duplication set is as you say a single character swapped round like you indicate, the principle is to extract the SAME AS records as you point out and remove them. The logic being to identify the row to extract and leave you with the result set you want. So.. how can this be done?.

Each character will have an ASCII representation, a numeric value. 'A'=65 and B='66' Added together they make 131 so over two rows we are going to see two values of 131.

If we created a third column to store the ASCII value and worked on that we could extract out the MAXIMUM record ID identifying the row, extract those rows into a temporary table compare the tables against each other and remove the rows by the relevant SQL comparison on a LEFT INNER JOIN between the relevant tables.

One word of CAUTION here, The ascii values when summed will give a value remember that 8+5=12 so does 5+7 so the actual theory has limited scope
and is absolutely based on your data provided above, You do NOT want a summation giving you an erronous result value on which you base your delete.

Anyway below is a script I knocked up to assist you in demonstrating the HOWS!! not intended as the SOLUTION obviously as that is for you to deal with If you create a physical table ..like so

CREATE TABLE mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )

and populate it with data of the type you provided above and then run the script below in query analyser you will see that it works only on temporary tables, which you can adjust to suit your production environment when you are ready and happy it works for you.

--USE whateverdatabasenamehere --<<<replace database name with yours
SET NOCOUNT ON
--create a temporary table to store the values
CREATE TABLE #mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )
--and the insert into this table
INSERT #mytable
--values from the main production table
SELECT * from mytable --<<substitute your table name here the column orders must match
--then update the temporary table with --the ACSII numeric values representing the characters ie A and B summed together=131
UPDATE #mytable
SET asciivalue=ASCII(sap_customer_nbr) + ASCII(sap_customer_nbr1)
--create another temporary table
CREATE TABLE #mylink (
[asciivalue] [int] null ,
[recid] [int] null )
--into which we will insert
INSERT #mylink
-- the MAXIMUM RecordID (RecID) for each Asciivalue which essential only gives
-- ACTUAL recordID to identify the row that we wish ultimately to Delete from the table
SELECT #mytable.asciivalue, MAX(#mytable.recid) AS linkkey
FROM #mytable
GROUP BY #mytable.asciivalue

--the next two lines are left in to merely show you in query analyser the resultsets
SELECT * FROM #mytable
SELECT * FROM #mylink

--we now delete from the first temporary table the rows we wish to remove
DELETE #mytable
FROM #mylink INNER JOIN #mytable ON #mylink.recid = #mytable.recid
--and to finalise we show the results again in query analyser
SELECT * FROM #mytable
--and finally drop the temporary explicitly
DROP TABLE #mylink
DROP TABLE #mytable
i have not read the entire solution, but i have one comment, 8 + 5 <> 12...
Aug 24 '07 #3
Jim Doherty
897 Expert 512MB
i have not read the entire solution, but i have one comment, 8 + 5 <> 12...
It is where I come from hahahaha
Aug 24 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Wolfgang Lipp | last post by:
From: Lipp, Wolfgang Sent: Tuesday, 27?January?2004 13:26 <annotation> the first eleven contributions in this thread started as an off-list email discussion; i have posted them here with...
4
by: Fughal | last post by:
Hi, I have a big DB2 database and I need this database without any Data in it for testing something. I have made a backup of these db and restore it on a testing system. My Problem is now how...
2
by: RingTone | last post by:
SQL Server 2000/Access 2000 linked tables PK = FLOOR/DESK NO/TYPE A User has complained about seeing data below simply by scrolling through the attached table, the data is there when you look at...
13
by: Bob Darlington | last post by:
I have a repair and backup database routine which runs when a user closes down my application. It works fine in my development machine, but breaks on a client's at the following line: If...
6
by: Martin Bischoff | last post by:
Hi, I'm creating temporary directories in my web app (e.g. ~/data/temp/temp123) to allow users to upload files. When I later delete these directories (from the code behind), the application...
2
by: SiouxieQ | last post by:
Hi there, I'm using the code below to try to delete a name from a list of names in a file. Unfortunately it doesn't quite do what I want it to. Instead of looking for the name in the...
1
by: Pat | last post by:
Hi all, I have a really awkward situation that is causing memory leak problems. I'm passing data to a driver, and unfortunately, the driver code is not something I can change, and it is written...
3
by: gemguy | last post by:
hi, I have a issue with an array values... Im having values repeatedly stored in an array and i want to delete those repeated values using javascript. * I tried it by storing in an array and...
1
by: Kyosuke18 | last post by:
Hi everyone, I have a problem in deleting a data that is connected on the database.. I tried this code but it shows me an error: Run-time error '-2147217900(80040e14)': Syntax error in string in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.