I have 2 tables table1 has id column which is just an auto increment number table2 has id column which is the same but begins with "vt_" then the number I need to Delete any row on table2 where the table2.id doesn't match the table1.id BUT I'm stuck because I need to replace the "vt_" before I can compare the 2 numbers. I know I can do it in an SELECT, like this "SELECT REPLACE(table2.id,'vts_','') AS table2_id, " I can't get it to work, my last attempt was like this: $sql = @mysql_query("DELETE table2 FROM table2 INNER JOIN table1 ON REPLACE(table2.id,'vts_','') = table1.id");
Make sure to do a backup before running this. Is it vt_ or vts_? If vt_, use this, otherwise vts_ replace the 4 with a 5. DELETE FROM table2 WHERE id NOT IN (SELECT SUBSTRING(id,4) FROM table1)