The situation is the following, there is a record in the table:

id user_id field_id value 1 16 6 test 2 16 6 test2 3 16 7 test3 4 16 6 test

It is necessary in one query to remove from this table the rows where there is at least 1 copy of "user_id + field_id".

Those. two or more lines cannot exist where the "user_id" and "field_id" fields match.

As a result, the lines with id 1 and 4 should be deleted.

For early thanks for the help.

  • > For earlier MySQL in advance , if I remember correctly, prohibits changes to the table based on data obtained from the same table. This is completely solved through a double select, if not simpler, but perhaps it is still worth splitting into two requests (if, of course, such a possibility exists). - etki
  • Maybe. This possibility exists. But it seemed to get out. - MatthewP

1 answer 1

It is necessary to unscrew through delete inner join . Like that:

 delete t1.* from table as t1 inner join ( select concat(cast(field_id as varchar), value) as s ) t2 on concat(cast(t1.field_id as varchar), t1.value)=t2.s
  • To be honest, this is complicated for my understanding. I think after 10 hours of meditation, I found the right request. SELECT id FROM table as f WHERE EXISTS (SELECT * FROM table as ff WHERE f. User_id = ff.user_id AND f.