question

Yasemin avatar image
Yasemin asked

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-sqldelete
10 |1200 characters needed characters left characters exceeded

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

aRookieBIdev avatar image
aRookieBIdev answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

@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 Like 1 ·
+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 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
Show more comments
Cyborg avatar image
Cyborg answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

Unfortunately this doesnt work either. attributestring table doesnt have an entryid field. and without this your query returns 545 records. Thanks
0 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
Leo avatar image
Leo answered
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 characters needed characters left characters exceeded

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

Kev Riley avatar image
Kev Riley answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
ozamora avatar image
ozamora answered
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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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