MySQL - Delete with multiple conditions

Question

I have a database with properties and I need to delete all the properties except for those "owned" by either one of 3 "Inscription Agents". Each property can have up to 2 "owners": agent_inscripteur_1 and agent_inscripteur_2 on the database. This is my code:

DELETE FROM inscriptions 
WHERE (agent_inscripteur_1 != 100520 OR agent_inscripteur_2 != 100520) 
      AND (agent_inscripteur_1 != 97927 OR agent_inscripteur_2 != 97927) 
      AND (agent_inscripteur_1 != 99237 OR agent_inscripteur_2 != 99237)

I think what is happening in my case is that the first part of the code before the AND is executed before the rest, so all properties except for those listed by the first agent are being deleted (by the time it gets to the second and third agent, the properties are all gone).

Can someone please point me in the right direction?

Thanks!

Actually, in SQL the db has no concept of "first" for Boolean conditions (CASE is an exception for a couple of reasons...). Since for each row at least one of the sub-conditions will (likely) be true, the row is deleted. DBs do this for a couple of reasons: 1) it's actually due to dbs thinking in "sets", essentially matching records 2) it allows the db to choose which condition to evaluate first, allowing queries to be faster. — Clockwork-Muse
– Clockwork-Muse, Commented Jun 13, 2014 at 7:06

Rahul · Accepted Answer · 2014-06-12 18:25:04Z

3

What if you simply change your condition like below using NOT IN instead

DELETE FROM inscriptions 
WHERE agent_inscripteur_1 NOT IN (100520,97927,99237)
OR agent_inscripteur_2 NOT IN (100520,97927,99237)

edited Jun 12, 2014 at 18:25

answered Jun 12, 2014 at 18:15

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Palmer Del Campo Over a year ago

Thanks, but this deletes 0 rows.

Rahul Over a year ago

@PalmerDelCampo, change the AND to OR. Most probably the condition is not matching. See edit.

Palmer Del Campo Over a year ago

That works, thanks. Please edit your answer to "or" and I'll mark it as correct.

A l w a y s S u n n y · Accepted Answer · 2014-06-12 18:15:47Z

1

Try out this,

DELETE FROM inscriptions 
WHERE ( 
(agent_inscripteur_1 != 100520 AND agent_inscripteur_2 != 100520) 
OR (agent_inscripteur_1 != 97927 AND agent_inscripteur_2 != 97927)
OR (agent_inscripteur_1 != 99237 AND agent_inscripteur_2 != 99237)
)

answered Jun 12, 2014 at 18:15

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

1 Comment

Clockwork-Muse Over a year ago

No, this will still delete records he wants to keep.

user3703582 · Accepted Answer · 2014-06-12 18:22:18Z

1

I believe the simplest approach when attempting to use negative logic is to write the inverse and then prefix with not. Identify the records of concern and then do not delete them, something like this

DELETE FROM inscriptions 
WHERE not ( (agent_inscripteur_1 = 100520 OR agent_inscripteur_2 = 100520) 
      or (agent_inscripteur_1 = 97927 OR agent_inscripteur_2 = 97927) 
      or (agent_inscripteur_1 = 99237 OR agent_inscripteur_2 = 99237))

You could simplify the query to something like this

DELETE FROM inscriptions 
WHERE not (   agent_inscripteur_1 in (100520, 97927, 99237) 
           OR agent_inscripteur_2 in (100520, 97927, 99237) 
           )

answered Jun 12, 2014 at 18:22

user3703582

Comments

K.K.Agarwal · Accepted Answer · 2014-06-13 05:58:32Z

1

I think you can use here not in operator

DELETE FROM inscriptions WHERE agent_inscripteur_1 not in(100520,97927,99237) 
OR 
agent_inscripteur_2 not in(100520,97927,99237)

edited Jun 13, 2014 at 5:58

answered Jun 12, 2014 at 18:16

K.K.Agarwal

8565 silver badges10 bronze badges

1 Comment

Palmer Del Campo Over a year ago

Thanks but this deletes 0 rows as well. Bump.

Collectives™ on Stack Overflow

MySQL - Delete with multiple conditions

4 Answers 4

3 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related