where clause in delete statement with two conditions

Question

question

Yasemin asked Oct 25, '10

where clause in delete statement with two conditions

I need to delete distinct recordIDs from EntryAttribute table where the record is tagged with two options (attributeid = '513367ff-de82-4f84-b982-8c4b854a08e1' and c74ce292-06db-420e-83a6-a32d84fefa28') I know that there are 159 records tagged with both values but when I run my delete statement I get over 2000 records? Can you help please. Many thanks **This is my query:**


Delete a from EntryAttribute as a 
where exists (Select distinct x.entryid from entryattribute as x
			inner join attributestring as y on y.attributeid = x.attributeid
			where x.entryid = a.entryid
			and y.attributeid = '513367ff-de82-4f84-b982-8c4b854a08e1')---Reg S
and exists (select distinct z.entryid from entryattribute as z
			inner join attributestring as za on za.attributeid = z.attributeid
			where z.entryid = a.entryid
			and za.attributeid = 'c74ce292-06db-420e-83a6-a32d84fefa28')---Rule 144A

t-sql delete

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-10-25T04:04:54Z

aRookieBIdev answered Oct 25, '10

Please remove the delete and try select query with the same where clause to check the resul it thats what you would like to delete. Thanks, Kannan

7

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Oct 25, 2010 at 04:46 AM

@Yasemin, the select query is retuning 159 `DISTINCT` rows, the delete is telling you how many rows where deleted... what happens when you drop the `DISTINCT` from the select?

1 ·

Kev Riley ♦♦ commented · Oct 25, 2010 at 04:09 AM

+1 @Kannan : @Yasemin, how do you know there are 159 records to delete? Have you a select query that returns those? In that case rework the same query to be a delete rather than a select

0 ·

Yasemin commented · Oct 25, 2010 at 04:39 AM

Yes I have tried the Select query and it returns 159, which is: select distinct a.entryid, c.attributevalue from entry as a inner join entryattribute as b on b.entryid = a.entryid inner join attributestring as c on c.attributeid = b.attributeid where c.attributeID = '513367ff-de82-4f84-b982-8c4b854a08e1'---Reg S and exists (select * from entryattribute as x inner join entryattribute as y on y.entryid = x.entryid inner join attributestring as z on z.attributeid = y.attributeid where x.entryid = a.entryid and z.attributeid = 'c74ce292-06db-420e-83a6-a32d84fefa28')---Rule 144A but the delete returns 2000+ ?

0 ·

Kev Riley ♦♦ commented · Oct 25, 2010 at 04:51 AM

it's good that you are checking and are wary of a difference in expected results, but be careful that you are comparing like with like.....

0 ·

Yasemin commented · Oct 25, 2010 at 04:53 AM

Even if I drop the DISTINCT from the select, it still returns 159. because I am searching for records tagged to both of the attributeIDs specified in my where and where exists clause..

0 ·

Kev Riley ♦♦ commented · Oct 25, 2010 at 04:56 AM

no - your select is joined to an `entry` table, so again it's not really returning the same subset of data that is being worked on by the delete

0 ·

Yasemin commented · Oct 25, 2010 at 06:37 AM

Even if I dont join to the entry table and I use this select query, I get 159 select a.entryid, c.attributevalue from entryattribute as a inner join attributestring as c on c.attributeid = a.attributeid where c.attributeID = '513367ff-de82-4f84-b982-8c4b854a08e1'---Reg S and exists (select * from entryattribute as x inner join entryattribute as y on y.entryid = x.entryid inner join attributestring as z on z.attributeid = y.attributeid where x.entryid = a.entryid and z.attributeid = 'c74ce292-06db-420e-83a6-a32d84fefa28')---Rule 144A

0 ·

Answer 2 · 2010-10-25T04:07:16Z

Cyborg answered Oct 25, '10

A wild try


DELETE EntryAttribute 
FROM EntryAttribute AS t1 
INNER JOIN attributestring  AS t2
on t1.attributeid = t2.attributeid
and 2 = (SELECT COUNT(DISTINCT AttributeID
         FROM attributestring  t3
         WHERE t3.attributeid = t1.attributeid 
         and attributeID IN('513367ff-de82-4f84-b982-8c4b854a08e1' , 'c74ce292-06db-420e-83a6-a32d84fefa28')

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yasemin commented · Oct 25, 2010 at 04:21 AM

Unfortunately this doesnt work either. attributestring table doesnt have an entryid field. and without this your query returns 545 records. Thanks

0 ·

Cyborg commented · Oct 25, 2010 at 04:41 AM

Yesmin, may be i might be missing a join predicate try running select query by using inner join to link those tables. don't miss any join predicate since it will return more records (ie self join). Once you got the required rows then replace the select columns with Delete from TableName.

0 ·

Yasemin commented · Oct 25, 2010 at 04:49 AM

If I say where t2.AttributeID IN ('513367ff-de82-4f84-b982-8c4b854a08e1' , 'c74ce292-06db-420e-83a6-a32d84fefa28') this would mean either one like OR, but I want to make sure the query returns only the records tagged with both values and that's why I have used the where exists twice...

0 ·

Answer 3 · 2010-10-25T04:47:00Z

Leo answered Oct 25, '10

Try this - --Check First before you Delete to make sure you are going to delete the correct record
SELECT A.*
From EntryAttribute A INNER JOIN attributestring Y
ON A.attributeid = = Y.attributeid AND A.entryid = Y.entryid
WHERE (y.attributeid = '513367ff-de82-4f84-b982-8c4b854a08e1')
AND (z.attributeid = 'c74ce292-06db-420e-83a6-a32d84fefa28')
--If you happy with it, DELETE
Delete A
From EntryAttribute A INNER JOIN attributestring Y
ON A.attributeid = = Y.attributeid AND A.entryid = Y.entryid
WHERE (y.attributeid = '513367ff-de82-4f84-b982-8c4b854a08e1')
AND (z.attributeid = 'c74ce292-06db-420e-83a6-a32d84fefa28')

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-10-25T04:49:24Z

Kev Riley answered Oct 25, '10

Whilst I don't particularly like the query, without some data and schemas is difficult to get it 100% correct, but why are you joining to another table in the exists subqueries? Delete from EntryAttribute as a where exists (Select x.entryid from entryattribute as x where x.entryid = a.entryid and x.attributeid = '513367ff-de82-4f84-b982-8c4b854a08e1')---Reg S and exists (select z.entryid from entryattribute as z where z.entryid = a.entryid and z.attributeid = 'c74ce292-06db-420e-83a6-a32d84fefa28')---Rule 144A and why bother with the distinct - the exists will just return true or false - it doesn't care how many rows - once there is more than one then it's TRUE

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yasemin commented · Oct 25, 2010 at 06:40 AM

This seem to work: delete a from entryattribute as a inner join attributestring as c on c.attributeid = a.attributeid where c.attributeID = '513367ff-de82-4f84-b982-8c4b854a08e1'---Reg S and exists (select * from entryattribute as x inner join entryattribute as y on y.entryid = x.entryid inner join attributestring as z on z.attributeid = y.attributeid where x.entryid = a.entryid and z.attributeid = 'c74ce292-06db-420e-83a6-a32d84fefa28')---Rule 144A

0 ·

Answer 5 · 2010-10-25T06:36:32Z

ozamora answered Oct 25, '10

My approach is to always try minimizing locks -- Dump the records to be delete into a temp SELECT A.entryid INTO #temp_delete From EntryAttribute A INNER JOIN attributestring Y ON A.attributeid = = Y.attributeid AND A.entryid = Y.entryid WHERE (y.attributeid = '513367ff-de82-4f84-b982-8c4b854a08e1') AND (z.attributeid = 'c74ce292-06db-420e-83a6-a32d84fefa28'); -- Add a primary key to the temp ALTER TABLE #temp_delete ADD PRIMARY KEY (entryid); -- Delete BEGIN TRAN; DELETE a FROM #temp_delete t INNER JOIN EntryAttribute a ON (t.entryid = a.entryid); COMMIT; Good Luck

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question