delete from mysql with condtion

Question

how can I delete rows from this table in mysql database that after the delete I should have 4 distinct values in the column valeurs .

the actual state of my database :

+----+-----------+---------+---------------------+
| id | action_id | valeurs | temps_action        |
+----+-----------+---------+---------------------+
| 81 |         1 | ON_1    | 2016-04-26 11:14:43 |
| 80 |         2 | OFF_2   | 2016-04-26 11:14:41 |
| 84 |         2 | OFF_2   | 2016-04-26 11:14:48 |
| 83 |         1 | ON_1    | 2016-04-26 11:14:46 |
| 79 |         1 | OFF_1   | 2016-04-26 11:14:40 |
| 78 |         2 | ON_2    | 2016-04-26 11:14:38 |
| 77 |         1 | ON_1    | 2016-04-26 11:14:35 |
| 82 |         2 | OFF_2   | 2016-04-26 11:14:45 |
+----+-----------+---------+---------------------+

I want to end up with :

+----+-----------+---------+---------------------+
| id | action_id | valeurs | temps_action        |
+----+-----------+---------+---------------------+
| 81 |         1 | ON_1    | 2016-04-26 11:14:43 |
| 80 |         2 | OFF_2   | 2016-04-26 11:14:41 |
| 79 |         1 | OFF_1   | 2016-04-26 11:14:40 |
| 78 |         2 | ON_2    | 2016-04-26 11:14:38 |
+----+-----------+---------+---------------------+

Possible duplicate of [How to delete duplicates on a MySQL table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table) — Mr. Llama, Apr 26 '16 at 20:25

ScaisEdge · Answer 1 · 2016-04-26T20:56:10.303

1

You can use this way

delete from   my_table  as t
where (t.id, t.valeurs) not  in ( select max(t2.id), t2.valeurs 
                            from my_table as t2
                             group by valeurs);

edited Apr 26 '16 at 20:56

answered Apr 26 '16 at 20:25

ScaisEdge

131,976
10
91
107

Error - You can't specify target table '...' for update in FROM clause – Max P. Apr 26 '16 at 20:33
I know, I get this error when I try to execute this query – Max P. Apr 26 '16 at 20:42
@MaxP. I think solved with an alias .. so the delete table and the subselect are notb the same – ScaisEdge Apr 26 '16 at 20:46
What means `OP`? Sorry for question – Max P. Apr 26 '16 at 20:48
@MaxP. Original Poster – ScaisEdge Apr 26 '16 at 20:48
@scaisEdge hehe, isn't that always the case lately? They just *Poof!* away, running off with the rabbit from the magic hat. – Funk Forty Niner Apr 26 '16 at 20:49
1

@Fred-ii- :) Anyway if this is not useful for the OP seems useful for the others answer authors.. and sometime is useful for me too – ScaisEdge Apr 26 '16 at 20:51
you delete only one row for every valeurs, but i think OP wants to save just ones – splash58 Apr 26 '16 at 20:51
@splash58 right i hanven't see the last OFF_2 then the not in should solve the problem .. i have upodate the answer . – ScaisEdge Apr 26 '16 at 20:56
well, and you have to rewrite my answer, because now you don't save `on` and `off` for every `action_id` :))) – splash58 Apr 26 '16 at 20:59
Error, http://sqlfiddle.com/#!9/6780b, add your query at the and and try to build schema. I think that it is impossible without temporary table – Max P. Apr 26 '16 at 21:02
@MaxP. thank for sqlfiddle i have tried with this select * from rtrtrt as t where (t.id, t.valeurs) not in ( select max(t2.id), t2.valeurs from rtrtrt as t2 group by valeurs); so i can see the rows selectd for delete and work right .. – ScaisEdge Apr 26 '16 at 21:07

splash58 · Answer 2 · 2016-04-26T20:41:27.023

1

such query saves the rows with max temps_action for each action_id, valeurs

delete 
    from  my_table 
  where (action_id, temps_action, valeurs) not in 
      (select * from (select action_id, max(temps_action), valeurs 
             from my_table 
          group by action_id, valeurs) t1);

edited Apr 26 '16 at 20:41

answered Apr 26 '16 at 20:29

splash58

26,043
3
22
34

#1093 - You can't specify target table '...' for update in FROM clause – Max P. Apr 26 '16 at 20:36
@Max P. it can be made by adding a subquery – splash58 Apr 26 '16 at 20:49
@MaxP. You are not lazy :) – splash58 Apr 26 '16 at 21:09

score 0 · Answer 3 · edited Apr 26 '16 at 20:43

0

What you're really trying to do is to remove duplicate rows. Here's an example query that could help you with that:

DELETE FROM TableName  
WHERE valeurs IN (SELECT * 
             FROM (SELECT valeurs FROM TableName 
                   GROUP BY valeurs
 HAVING (COUNT(*) > 1) AS Something
                  ) 
            );

edited Apr 26 '16 at 20:43

Funk Forty Niner

74,450
15
68
141

answered Apr 26 '16 at 20:35

Auguste

2,007
2
17
25

error #1248 - Every derived table must have its own alias – Max P. Apr 26 '16 at 20:37

delete from mysql with condtion

3 Answers3