Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

SQL to conditionally delete similiar records

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
Xbradders
Asked:
Xbradders
1 Solution
 
Kent DyerIT Security Analyst SeniorCommented:
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
 
XbraddersAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now