Issues with DELETE
Date: July 11, 2011 04:35PM
I am running into this weird issue with the DELETE DML statement. The table has the following attributes:
TAGS {
id INT (primary key, auto_increment),
url_id VARCHAR(30) (foreign key, ON DELETE NO ACTION, ON UPDATE NO ACTION),
tag VARCHAR(20)
}
The schema uses InnoDB engine and utf8 charset
Now, lets assume the following tuples are present in the table (along with corresponding tuples in the table referenced by the foreign key):
id url_id tag
-- ------ ---
1 PWR-10 power
2 EQPT-23 signal
3 MGMT-39 quick lookup
4 QOS-40 fast route
When I try deleting rows using the primary key, it works perfectly fine. When I use the DELETE statement with conditions, there are issues. For example,
DELETE FROM tags WHERE url_id = 'PWR-10' AND tag = 'power'
works perfectly fine and deletes the row with id = 1. On the other hand,
DELETE FROM tags WHERE url_id = 'MGMT-39' AND tag = 'quick lookup' fails to delete the row (id = 4). Similarly, the row with id = 3 (tag: quick lookup) also doesn't get deleted.
What I observe is that if the field tag has more than one word (i.e. presence of blank spaces), the query does not delete the intended row.
Am I going wrong in the syntax or is there something which I'm overlooking?
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.