I have a situation where I have a two field table, id and value. I want to delete a row if there is a value with the same key and a particular second value.
like
id, val
1, val1
1, val2
delete val 1 from the table when val2 exists with the same key.
I tried this (and a bunch of other combinations) and none seem to work
delete from table t1 where t1.val = ‘val1’ and t1.id in (select id from table t2 where t2.val = ‘val2’)
This format actually works for the select statement, i.e. replace delete with select *.
Okay, I’ll do it in PHP. Thanks for the input, I appreciate it.
Yes, I understand.
I looked it up in the manual:
Currently, you cannot delete from a table and select from the same table in a subquery.
In PHP I’d do two queries: one to select the id’s with value 2, one to delete the rows with the id’s found and value 1.
To clarify further, I am trying to change all val1 to val2 so any id that has both won’t let me make the change. So in those cases I need to delete val1 rather than change it, then I can run the update query to change those that don’t already have a val2.
Yes, I am running it in phpmyadmin and it gives me a syntax error. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
It’s actually a lookup table so the id I am referring to is the id of another table so the unique key is the id and the value allowing multiple values per id, but they must be unique.
What happens when you run that delete query? An error? If so, what error?
And a question: how comes you have duplicate id’s in that table? An error, or are they supposed to be not unique?