Please Help with Delete Statement

[expired user #5734]'s profile image [expired user #5734] posted 13 years ago in Running SQL scripts Permalink
Hello all,
I am somewhat new to sql queries, but I've always been able to easily work my way around with the use of tutorials and such on the internet. However, I have come across an issue that has me stumped that I have been searching the internet for 3-4 days on and simply cannot find a solution. Here is my issue. I need to delete some rows from a database that meet certain criteria. My statement looks somewhat like this:

DELETE FROM table1 WHERE column_2 = 'x' AND column_3 = 'y' AND column_4 = 'z';

Now, if I execute this query, it will NOT delete that entry, but will also not generate any errors. From what I have read the "AND" statement has a max of only being able to compare 2 columns/values. Therefor by putting the 3rd value in the syntax, it is essentially voiding the "AND" statements and returning 0 rows. I need to do maintenance on multiple databases, but I cannot delete rows without having unique identifiers. I need to be able to compare (in one row) 3 separate columns to create my unique identifier. With sql syntax being so complex, I really find it hard to believe that such a feature would be limited to 2. That being said, I know I am just missing something. Does anyone have a work around for this situation? Thanks in advanced :)

Raydeon
ansgar's profile image ansgar posted 13 years ago Permalink
Your DELETE looks ok so far. If you see "0 rows affected" in the SQL log, the values are not matching. You should use the data tab, find these rows and press Del to delete them. This should generate the values correctly escaped and whatever is in there.
[expired user #5734]'s profile image [expired user #5734] posted 13 years ago Permalink
The problem is that it is not working. I am working out of a database that is 170,000+ entries. I have verified that the entries are in fact in the database exactly how I entered the the statement. It still comes back with "0 rows effected". If I remove one of the "and" statements to look like this:

DELETE FROM table1 WHERE column_2 = 'x' AND column_3 = 'y';

Then it works perfect. The problem is I need the 3rd variable to correctly identify the specific entries I want to remove. I can effectively filter these and remove them easily. The problem with that is this is for a project that has multiple people using the same database entries. I am trying to create an "update.sql" that will effectively update all of the different databases to be identical (if that makes sense). I read in the MySql official manual that when using the "and" statement, that two clauses is the max. There has to be some way of working around that or a more effective/complex clause than "and".

Raydeon
[expired user #5734]'s profile image [expired user #5734] posted 13 years ago Permalink
Nevermind, Anse you are correct. I did have the statement correct and it should work like that. I actually just pinpointed my issue. In one of the columns I need to identify data in, it has a decimal point like 0.32 . I believe I have my statement incorrect for mysql to correctly identify a number with a decimal point. Sorry for the confusion and thank you for your help Anse!

Raydeon
ansgar's profile image ansgar posted 13 years ago Permalink
No problem.

Indeed that's why I said try the data tab :)
kalvaro's profile image kalvaro posted 13 years ago Permalink
You don't say how you verified there were matching rows but the obvious approach is to run the same query but replacing the DELETE clause with SELECT *:

SELECT * FROM table1 WHERE column_2 = 'x' AND column_3 = 'y';

Please login to leave a reply, or register at first.