Solved

SQL to conditionally delete similiar records

Posted on 2014-02-07
2
296 Views
Last Modified: 2014-02-07
I've got a really tricky SQL script to write to clean up some data.   I'll try to explain it. Can anyone help with this:

I have a table called Products, that I've simplified below.

ID     Product                     Manu              Color
1       xyz123_01                  Acme              Green
2       xzy123_01_1               Acme             Green
3       xyz123_01_2               Acme              Green
4       abc100_05                 Jones Co.          Red
5       abc100_05_1                Jones Co.           Pink

I need to create a script that will delete records #2 and #3 here, because they share Manu, Color and the first 9 characters of Product field as #1.  Record #1 should remain though.

Products #4 and #5 would both remain, because they have differing Color fields.

The resulting table, called Products2 should look like this:

ID     Product                     Manu              Color
1       xyz123_01                  Acme              Green
4       abc100_05                 Jones Co.          Red
5       abc100_05_1                Jones Co.           Pink
0
Comment
Question by:Xbradders
2 Comments
 
LVL 17

Accepted Solution

by:
Kent Dyer earned 400 total points
ID: 39842585
SELECT DISTINCT LEFT(PRODUCT, 9), MENU, COLOR FROM SOME_TABLE

Then you could do something like..

SELECT * FROM SOME_TABLE
WHERE NOT IN (SELECT DISTINCT LEFT(PRODUCT, 9), MENU, COLOR FROM SOME_TABLE)

Once you have the SELECT nailed down, then setup your delete.

Hope This helps.
0
 

Author Comment

by:Xbradders
ID: 39842788
Great, thanks Kent.  I've built my select, but can't seem to get the WHERE NOT IN clause to get the tracks to delete.  Forgive me, my sql is rusty and I'm in MySQL now which may not support this...  This is what I've tried:

SELECT * FROM test.sm_tracks
WHERE NOT IN
(SELECT distinct LEFT(CatNo,9),
Composer1, COMPOSER1_SHARE,
Composer2, COMPOSER2_SHARE,
Composer3, COMPOSER3_SHARE,
Composer4, COMPOSER4_SHARE,
Composer5, COMPOSER5_SHARE,
Composer6, COMPOSER6_SHARE,
PUBLISHER1, PUBLISHER1_SHARE,
PUBLISHER2, PUBLISHER2_SHARE,
PUBLISHER3, PUBLISHER3_SHARE,
PUBLISHER4, PUBLISHER4_SHARE,
PUBLISHER5, PUBLISHER5_SHARE,
PUBLISHER6, PUBLISHER6_SHARE,
ARRANGER1, ARRANGER1_SHARE,
ARRANGER2, ARRANGER2_SHARE,
ARRANGER3, ARRANGER3_SHARE,
ARRANGER4, ARRANGER4_SHARE,
ARRANGER5, ARRANGER5_SHARE,
ARRANGER6, ARRANGER6_SHARE
FROM test.sm_tracks);
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Where on a calculated field 1 30
SQL Select in Access 2003 3 42
Mysql how to execute a commands file 5 30
How to use 2 ON statements in inner join 3 20
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question