Delete From One Table Based on Two Columns in Another

Question

I need to delete values in a temp table based on both values in another. In other words, I do not want any item_desc left in B for an id that has a style_cd listed in A.

#tempA
style_cd char(3),
id_cd varchar(15)

Some sample values are like:

A, 123456
A, 654321
B, 321456
A, 654123
C, 424242

.

#tempB
item_desc varchar(50),
item_num char(4),
style_cd char(3),
id_cd varchar(15)

Sample values for this table:

item 1,  7, A, 123456
item 2, 14, B, 123456 
item 2, 14, A, 123456
item 3, 23, A, 123456
....snip....
item 2, 14, B, 654321
item 2, 14, A, 654321
item 6, 44, A, 654321

For example - for id 123456 I want to remove both entries for item 2 from B. Each item can have several styles associated with it. An id will usually only have one style associated with it but could conceivably have several.

I must be running into a block because this doesn't seem that complicated. I have been working on this for two days and can't get anything but all rows deleted. I have been trying something along the lines of:

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

Of course this deletes all the rows and I can't for the life of me figure out how to just get rid of only rows associated with the style_cd values listed for an id in Table A. I hope this makes sense and someone can put me out of my misery!!!!

I am using Sybase ASE 15.0.2

ADD... I used @john's suggestion as a starting point. When I test the select it picks out the rows I need for it to.

    SELECT a.style_cd
    FROM #tempB b
      JOIN #tempA a
      ON a.id_cd = b.id_cd
      AND a.style_cd = b.style_cd

When I add in any Delete clause it wipes out everything

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

Somehow I am not getting just the rows from the list in the select removed from tempB

score 2 · Accepted Answer · answered Aug 21 '12 at 22:00

2

It's just a simple confusion between DELETE #tempDB and DELETE *FROM* #tempDB

Try your same query modified like so:

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

answered Aug 21 '12 at 22:00

John Weldon

39,849
11
94
127

I added that FROM and got the same results. – djailer Aug 22 '12 at 19:06
Sybase must have a different syntax. https://gist.github.com/3428603 is my testcase I did in SQLite – John Weldon Aug 22 '12 at 19:34
That is weird. I pasted that in, formatted it for Sybase and it worked. I was left with one row for each of type B. I cut and pasted into my actual code and changed the table/column names. This time it did leave me with some rows, but just not what I expected. They all have the same style_cd now and I have to figure out what I got back compared to the original list. I might be getting closer!! – djailer Aug 22 '12 at 22:52
I see what it did now. It deleted any style_cd listed anywhere in #tempA. The only rows I had left was one which did not happen to show up in #tempA for this set of data. Not what I needed yet....but it is a start. – djailer Aug 22 '12 at 23:00
One style_cd I meant...I should have about 120 rows and only see the 21 rows now for the style_cd in tempB which did not show up in this version of tableA. I haven't even started yet on the requirement to delete other items which have different style_cd but were ordered by the same id. – djailer Aug 23 '12 at 16:58
I included some additional data in a comment on the github page. – djailer Aug 23 '12 at 21:21

score 2 · Answer 2 · answered Aug 22 '12 at 08:10

2

Try this:

DELETE FROM #tempB
WHERE id_cd IN 
    (SELECT a.id_cd
     FROM #tempB b, tempA a
     where b.style_cd = a.style_cd)

answered Aug 22 '12 at 08:10

Robert

25,425
8
67
81

Nope...I had 17 users who ordered items and the select query alone gave me 3268 results out of a total of 209 items ordered. Everything was deleted from tempB. – djailer Aug 22 '12 at 19:05
Which items will not delete, from your example? Each row has key (id_cd)from tempE to tempA, so I think everything will be delete. Do you have where clause in your query? – Robert Aug 22 '12 at 20:01
I tried it exactly you suggested...with the WHERE id_cd IN...etc. All items eventually get deleted. I modified it to match @John's suggestion below, but when I scaled it up to include more varied data it deleted everything from tempB that had a style_cd associated with in id in tempA. – djailer Aug 23 '12 at 16:42

djailer · Answer 3 · 2012-08-23T22:19:18.343

Here is what worked finally...

    SELECT a.style_cd, item_desc,rowindex
    into #deltable
    FROM #tempB b
      JOIN #tempA a
      ON a.id_cd = b.id_cd 
      AND a.style_cd = b.style_cd


delete from #tempB
where rowindex in
(select rowindex from #deltable)



delete from #tempB
where item_desc in(
select b.item_desc
from #tableB a
join #deltable b
on a.item_desc= b.item_desc

I added an identity column (rowindex) to #tempB and used it to do the delete. I still have to test it but so far....it works. Thanks for all the nudges.

Delete From One Table Based on Two Columns in Another

3 Answers3