Update query not effecting all rows in database

Question

I have the following query....

UPDATE vehicle_catalog SET parent_id = 0 WHERE parent_id = SUBSTR(id, 0, 5)

I need to set all parent_ids to 0 where the first 5 characters of id is the same as the parent_id. This is effecting 0 rows when I'm looking at the data and it should be effecting over 10,000.

Any ideas on why this wouldn't be effecting all rows?

UPDATE What I don't understand if I did UPDATE SET col = '1' it would effect the whole database, yet my query isn't run on the whole database.

UPDATE The proper solution:

UPDATE `vehicle_catalog` SET parent = 0 WHERE SUBSTRING(id FROM 1 FOR 6) == SUBSTRING(parent_id FROM 1 FOR 6)

Answer 1

just a guess, but

WHERE parent_id = SUBSTR(id, 0, 5), or, should it be...

WHERE SUBSTR(parent_id, 0, 5) = SUBSTR(id, 0, 5)

Answer 2

your update will only affect a single row as written. for instance, if the ID was 12345678, your query would only update row 12345. You might try this

UPDATE vehicle_catalog SET parent_id = 0 
WHERE parent_id like concat(SUBSTR(id, 0, 5), "%");

Answer 3

SUBSTR() in MySQL uses 1-based indexing. Try this:

UPDATE vehicle_catalog SET parent_id = 0 WHERE parent_id = SUBSTR(id, 1, 5)

Also, see my comment on using a SELECT query to verify that your condition is correctly constructed.