delete from another 2 tables when delete from a table based on 2 field condition

Question

In MySQL server, I have 3 tables called user, thing, thingfile

user
uid, username, name, location, version

thing
thingid, username,, version

thingfile
thingid, code, version

Each user has many things and each thing has many thingfiles.

I am going to delete from user table like below:

delete FROM user a WHERE a.username NOT IN (
SELECT b.by_user
FROM bp_userlist b)
and  a.version="823"

Updated: before delete: user:

uid username  name location version
 1   abc       abc   abcdd   O
 2   abc       abc   abcdd   823
 3   ddd       ddd    dddd   823

thing

 thingid  username  version

1       abc        O
2       abc        O
1       abc        823
2       abc        823
3       ddd        823
1       ddd         823

thingfile
thingid  code version
 1        ee    O
 2        eed    O
 1        ee     823
 2        eddd    823
 3        fff    823

after delete: user:

uid username  name location version
 1   abc       abc   abcdd   O
 3   ddd       ddd    dddd   823

thing

 thingid  username  version

1       abc        O
2       abc        O
3       ddd        823
1       ddd         823


thingfile
thingid  code version
 1        ee    O
 2        eed    O
 3        fff    823

I want those records belonging to thingid in thing and thingfile tables be deleted as well. Is it possible without foreign key constrains?I have more than 10 tables and so many join doesnt work in one query.

Please share the output you want to get and what you have already tried to do this. — Abhishek Ginani, Nov 08 '15 at 15:18
Without foreign key constrains, you'll have to include two more queries to delete from those two tables. Otherwise, how do you expect MySQL to know what rows to delete without defining the relationship somehow? — Racil Hilan, Nov 08 '15 at 15:21
@RacilHilan I am Ok to add as many queries as needed! without adding foreign key constrains — MT467, Nov 08 '15 at 22:24
OK, so you need to tell us how you want to delete from the other two tables. It looks like you're deleting all records where `version=823`, but is there any other condition? — Racil Hilan, Nov 08 '15 at 22:33
@RacilHilan when I delete username="abc" and "version="823" I want all other thingid and thingfiles related to username="abc" with version="823" be deleted as well. — MT467, Nov 08 '15 at 22:48
Yes, yes. That's clear... Just wanted to make sure there is nothing else... — Racil Hilan, Nov 08 '15 at 22:50
You can't relate to the username. That's just not going to work. You have to relate to the PRIMARY KEY - in this case the user id. — Strawberry, Nov 08 '15 at 22:56
@Strawberry First of all there is no user id in the "thing" table. If there was one, life would be easier :-). Secondly, why can't you relate to the "username"? Maybe not ideal, but sure you can. — Racil Hilan, Nov 08 '15 at 23:00
@RacilHilan Either usernames change - in which case they can't be used, or they don't - in which case, they're PRIMARY, and the user id can be dispensed with altogether, — Strawberry, Nov 08 '15 at 23:14
@Strawberry That's in the ideal world. In his case, the structure is not ideal and it seems he's just trying to work with it. — Racil Hilan, Nov 08 '15 at 23:20
yeah, the structure is not perfect, I know and I already have a lot of data based on this structure and got foreign key error when I wanted to add one. — MT467, Nov 08 '15 at 23:35
@Strawberry If you read the question and his comments carefully, you will know what I know. No magic here. I've seen such situations more times that I care to count :-). If you haven't seen such a situation before, consider yourself lucky :-). — Racil Hilan, Nov 08 '15 at 23:40
@RacilHilan Then you should know that the solution is ALWAYS to fix the structural problems before proceeding ;-) (and you should know how to delete from multiple tables simultaneously!!) — Strawberry, Nov 08 '15 at 23:45
@Strawberry Am I talking Chinese here? Yes you know the right thing to do, but you're not allowed to do it for a million possible reasons. You cannot touch the db because it's in the hands of the wonderful DBA team :-)... or you're giving a task to fix a little thing in two hours, while changing the structure obviously takes much longer... or you tell your wonderful boss the db needs to be fixed and he says that it's been working for years... do you want me to go on? You're really lucky if you haven't been in one of those situations. — Racil Hilan, Nov 08 '15 at 23:59

Racil Hilan · Answer 1 · 2015-11-10T08:05:50.250

0

So the first query you already have. You need two more queries for the other two tables. However, you'll need to execute the three queries in reversed order, like this:

DELETE t FROM thingfile t
INNER JOIN thing a ON a.thingid = t.thingid
                  AND a.version = t.version
WHERE a.username NOT IN (
        SELECT b.by_user
        FROM bp_userlist b)
  AND a.version = '823';


DELETE a FROM thing a
WHERE a.username NOT IN (
        SELECT b.by_user
        FROM bp_userlist b)
  AND a.version = '823';


DELETE a FROM user a
WHERE a.username NOT IN (
        SELECT b.by_user
        FROM bp_userlist b)
  AND a.version = '823';

Here is a fiddle (Select "Text-only results" and run the query, seeing 3 results in text is easier than in grids).

edited Nov 10 '15 at 08:05

answered Nov 08 '15 at 22:56

Racil Hilan

24,690
13
50
55

1

@Strawberry He wants to delete records from 3 tables, so he needs 3 queries. How else can you do it? Post an answer if you have one. – Racil Hilan Nov 08 '15 at 22:58
1

The table structure is simply bad to make 1 query work for his problem. – CurseStacker Nov 08 '15 at 23:04
@RacilHilan in the first query, I think it should be t.version="823", right? – MT467 Nov 08 '15 at 23:57
No, `a.version = '823';` is the right way, but maybe `t.version` will work it depending on your db values. `a.version` is the right way because in that query you're deleting the thingfile children of the things that you will be deleting in the second query. And by the way, it should be single quotes, I will fix my answer. I copied your query and did not pay attention to the quotes. If the `version` is `integer` rather than `varchar`, then you should not use any quotes. – Racil Hilan Nov 09 '15 at 00:06
@RacilHilan: got mutual syntax error for the first query, although I can run it with select instead of delete, – MT467 Nov 09 '15 at 16:03
@RacilHilan and the first query doesnt return the correct rows – MT467 Nov 09 '15 at 18:14
What is exactly the syntax error you're getting? I just noticed the `thing.thingid` columns has duplicate values, aren't they supposed to be unique? Isn't it the primary key? If not, check my upadated answer. I included the `version` to the join in the first query. I also specified the table being deleted in all queries. It should work, see the fiddle at the end of the answer. – Racil Hilan Nov 10 '15 at 08:10

Strawberry · Answer 2 · 2015-11-08T23:30:26.907

@RacilHilan I'm not pretending that this exactly corresponds to the desired solution, but I'm presenting it by way of example that you can delete from several tables simultaneously (without using CASCADE ON DELETE)...

DROP TABLE IF EXISTS user;

CREATE TABLE user
(user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,version INT NOT NULL);

INSERT INTO user VALUES
(1,0),
(2,823),
(3,823);

DROP TABLE IF EXISTS thing;

CREATE TABLE thing
(thing_id INT NOT NULL
,user_id INT NOT NULL
,version INT NOT NULL
);

INSERT INTO thing VALUES
(1,1,0),
(2,1,0),
(1,1,823),
(2,1,823),
(3,2,823),
(1,2,823);

DROP TABLE IF EXISTS thing_file;

CREATE TABLE thing_file
(thing_id INT NOT NULL
,version INT NOT NULL
,PRIMARY KEY (thing_id,version)
);

INSERT INTO thing_file VALUES
(1,0),
(2,0),
(1,823),
(2,823),
(3,823);

SELECT * FROM user;
+---------+---------+
| user_id | version |
+---------+---------+
|       1 |       0 |
|       2 |     823 |
|       3 |     823 |
+---------+---------+

SELECT * FROM thing;
+----------+---------+---------+
| thing_id | user_id | version |
+----------+---------+---------+
|        1 |       1 |       0 |
|        2 |       1 |       0 |
|        1 |       1 |     823 |
|        2 |       1 |     823 |
|        3 |       2 |     823 |
|        1 |       2 |     823 |
+----------+---------+---------+

SELECT * FROM thing_file;
+----------+---------+
| thing_id | version |
+----------+---------+
|        1 |       0 |
|        1 |     823 |
|        2 |       0 |
|        2 |     823 |
|        3 |     823 |
+----------+---------+

DELETE u,t,f 
  FROM user u 
  JOIN thing t  
    ON t.user_id = u.user_id 
  JOIN thing_file f 
    ON f.thing_id = t.thing_id 
  WHERE 823 IN (u.version,t.version,f.version);
Query OK, 13 rows affected (0.00 sec)

SELECT * FROM user;
+---------+---------+
| user_id | version |
+---------+---------+
|       3 |     823 |
+---------+---------+
1 row in set (0.00 sec)

SELECT * FROM thing;
Empty set (0.00 sec)

SELECT * FROM thing_file;
Empty set (0.00 sec)

I have around 10 tables and join doesnt work this way, for simplicity I only posted 3 tables here... — MT467, Nov 09 '15 at 00:17
@MT467 We can only attempt to answer the question that's asked. — Strawberry, Nov 09 '15 at 00:18
@MT467 Hope in vain, I'm afraid - but if the question's clearer then, in all likelihood, I'll remove my dv. Oh, there's still no user_list table. I'll leave the dv where it is for now. — Strawberry, Nov 09 '15 at 18:32

delete from another 2 tables when delete from a table based on 2 field condition

2 Answers2