bostboy — 2010-10-07T03:06:35-04:00 — #1
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.
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 *.
bostboy — 2010-10-07T03:31:52-04:00 — #2
Okay, I'll do it in PHP. Thanks for the input, I appreciate it.
guido2004 — 2010-10-07T03:29:31-04:00 — #3
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.
bostboy — 2010-10-07T03:25:48-04:00 — #4
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.
bostboy — 2010-10-07T03:22:37-04:00 — #5
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.
guido2004 — 2010-10-07T03:16:51-04:00 — #6
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?