public
Created

  • Download Gist
test_12063689.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
DROP TABLE tempA; CREATE TABLE tempA ( style_cd CHAR(3), id_cd VARCHAR(15) );
DROP TABLE tempB; CREATE TABLE tempB ( item_desc VARCHAR(50), item_num CHAR(4), style_cd CHAR(3), id_cd VARCHAR(15) );
 
INSERT INTO tempA
SELECT 'A', 123456
UNION SELECT 'A', 654321
UNION SELECT 'B', 321456
UNION SELECT 'A', 654123
UNION SELECT 'C', 424242;
 
INSERT INTO tempB
SELECT 'item 1', 7, 'A', 123456
UNION SELECT 'item 2', 14, 'B', 123456
UNION SELECT 'item 2', 14, 'A', 123456
UNION SELECT 'item 3', 23, 'A', 123456
UNION SELECT 'item 2', 14, 'B', 654321
UNION SELECT 'item 2', 14, 'A', 654321
UNION SELECT 'item 6', 44, 'A', 654321;
 
SELECT '-------------BEFORE---------------';
SELECT * FROM tempB;
 
DELETE FROM tempB
WHERE style_cd IN (
SELECT b.style_cd
FROM tempB b
JOIN tempA a
ON b.id_cd = a.id_cd
AND b.style_cd = a.style_cd );
 
SELECT '--------------AFTER---------------';
SELECT * FROM tempB;

I modified to add more data....this is where it fails...
CREATE TABLE #tempA ( style_cd CHAR(3), id_cd VARCHAR(15) )
CREATE TABLE #tempB ( item_desc VARCHAR(50), item_num CHAR(4), style_cd CHAR(3), id_cd VARCHAR(15) )

INSERT INTO #tempA
      SELECT 'A', '123456'
UNION SELECT 'A', '654321'
UNION SELECT 'A', '321456'
UNION SELECT 'A', '654123'
UNION SELECT 'A', '424242'
UNION SELECT 'A', '135246'
UNION SELECT 'A', '642531'
UNION SELECT 'A', '315246'
UNION SELECT 'A', '642513'
UNION SELECT 'A', '444222'
UNION SELECT 'B', '222444'
UNION SELECT 'C', '111222'
UNION SELECT 'C', '222111'
UNION SELECT 'A', '333111'
UNION SELECT 'A', '111333'
UNION SELECT 'A', '666555'
UNION SELECT 'A', '555666'



INSERT INTO #tempB
      SELECT 'item 1', '7', 'A', '123456'
UNION SELECT 'item 2', '281', 'B', '123456'
UNION SELECT 'item 2', '281', 'C', '123456'
UNION SELECT 'item 2', '281', 'A', '123456'
UNION SELECT 'item 3', '319', 'B', '123456'
UNION SELECT 'item 3', '319', 'A', '123456'
UNION SELECT 'item 4', '370', 'C', '123456'
UNION SELECT 'item 4', '370', 'A', '123456'
UNION SELECT 'item 5', '373', 'D', '123456'
UNION SELECT 'item 5', '373', 'B', '123456'
UNION SELECT 'item 5', '373', 'A', '123456'
UNION SELECT 'item 6', '374', 'D', '123456'
UNION SELECT 'item 6', '374', 'B', '123456'
UNION SELECT 'item 6', '374', 'A', '123456'
UNION SELECT 'item 7', '377', 'B', '123456'
UNION SELECT 'item 7', '377', 'C', '123456'
UNION SELECT 'item 8', '163', 'D', '123456'
UNION SELECT 'item 8', '163', 'B', '123456'
UNION SELECT 'item 8', '163', 'A', '123456'
UNION SELECT 'item 9', '170', 'A', '123456'
UNION SELECT 'item 10', '182', 'B', '123456'
UNION SELECT 'item 11', '216', 'A', '123456'
UNION SELECT 'item 11', '216', 'B', '123456'
UNION SELECT 'item 11', '216', 'C', '123456'
UNION SELECT 'item 11', '216', 'D', '123456'
UNION SELECT 'item 12', '229', 'A', '123456'
UNION SELECT 'item 13', '236', 'C', '123456'
UNION SELECT 'item 13', '236', 'A', '123456'


--SELECT '-------------BEFORE---------------'
SELECT * FROM #tempB;

DELETE FROM #tempB
WHERE style_cd IN (
    SELECT b.style_cd
    FROM #tempB b
    JOIN #tempA a
      ON b.id_cd = a.id_cd
     AND b.style_cd = a.style_cd )

--SELECT '--------------AFTER---------------'
SELECT * FROM #tempB

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.