Subquery Delete in Mysql

Asked

Viewed 37 times

-4

I have the following table (Example), where the ID user 151 has 2 records:

id (Primary Key) user name
1 151
2 123
3 985
4 151
5 652

I need to delete this user’s 2 records. The problem is that as the column user name does not have Primary Key, Mysql does not allow because of safe update. I needed to get around this without having to touch the settings.

I tried in the following ways, but without success, the error persists.

DELETE FROM tabela WHERE id in (SELECT id FROM tabela WHERE coluna1 = 151);

DELETE FROM tabela WHERE id = (SELECT id FROM tabela WHERE coluna1 = 151);
php mysql query delete

  • I don’t understand your question !

  • Also this confused... DELETE FROM tabela WHERE usuario_id = '151' wouldn’t solve the problem? Will delete all records where usuario_id be 151.

  • That’s what I said, Mysql does not allow, because the user column has no Primary Key

  • It has nothing to do with Primary Key, what’s the mistake?

  • Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and Reconnect.

  • Execute SET SQL_SAFE_UPDATES = 0;. After executing the DELETE.

  • DELETE FROM tabela WHERE id = 151 doesn’t solve?

  • 1

    if the same table is only complicating, can do as commented here, DELETE FROM tabela WHERE usuario_id= 151, if there are two tables, your example is bad, because in the from both are called "table" which leads to error

  • Clarck’s answer worked, the rest couldn’t understand the question, but thank you anyway.

  • SET SQL_SAFE_UPDATES = 0; is recommended only to run in a tool, in Workbench for example, not to put in the code, if you are going to use this delete in the code you will need to change that

  • @Ricardopunctual yes, I got that. I opened another question focusing more on the subject, if I can help, thank you: https://answall.com/questions/514150/deletar-registro-sem-primary-key-no-mysql-via-ajax

  • 1

    It’s not because his answer decided that this correct, it worked by coincidence, turn off the SAFE_UPDATES nor should it be an answer, it became more like "technical support" and also has no sense to run a subquery without need, where the columns are specific to the same records, there is simply something wrong, it is much more likely that the problem is only occurring because there is data in other tables related to foreign keys, i.e., to delete a record in table A you have to delete the "linked".

  • ... but regardless of this the question does not present clear details for someone to be able to solve objectively and for the question to serve future users. We have years of experience here and so was created the guide, to help act on the site in a way that is beneficial to yourself, as well as to future visitors, making the content clear and profitable.

Show 8 more comments

1 answer

0

I need to delete this user’s 2 records. The problem is that like the user column id does not have Primary Key, Mysql does not allow for safe update account. I needed to get around this without having to touch settings.

It is possible to disable the SAFE UPDATE, execute the command DELETE and rehabilitate the restriction.

SET SQL_SAFE_UPDATES=0;
DELETE FROM tabela WHERE id in (SELECT id FROM tabela WHERE coluna1 = 151);
SET SQL_SAFE_UPDATES=1;

Browser other questions tagged php mysql query delete

You are not signed in. Login or sign up in order to post.