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
Please Help with Delete Statement
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
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
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
Raydeon
Please login to leave a reply, or register at first.