0

I am trying to delete records from WR_reported_Sites where the WR_reported_Sites.Site_Name is LIKE the Site_Name in the sites table. Here is what I have so far but it is not working. Any tips on how I can accomplish this? I am using mysql

DELETE FROM WR_reported_Sites WHERE WR_reported_Sites.Site_Name LIKE sites.Site_Name

doctorO20
  • 1
  • 2
    What exactly isn't working? Are you getting an error? The wrong rows deleted? – Mureinik Sep 05 '20 at 19:15
  • Does this answer your question? [Delete with Join in MySQL](https://stackoverflow.com/questions/652770/delete-with-join-in-mysql) – Will B. Sep 06 '20 at 03:20
  • I looked at that link you sent me and I am still not getting the results. It is similar to what Erick posted below and I am still getting a syntax error. Here is what I have tried and the resulting error: DELETE FROM WR_reported_Sites wr INNER JOIN sites s ON wr.Site_Name LIKE s.Site_Name MySQL said: Documentation #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 near 'wr INNER JOIN sites s ON wr.Site_Name LIKE s.Site_Name' at line 1 – doctorO20 Sep 06 '20 at 22:13

1 Answers1

0

Assuming that the sites table has an id column, and that a corresponding id column (or any other identification allowing to relate the tables (like site_name) you may use something like:

DELETE wr FROM wr_reported_sites wr
INNER JOIN sites s
ON wr.sites_id = s.id
WHERE wr.site_name LIKE s.site_name;

or if there are not id fields and you feel confortable with the site_names data you may try:

 DELETE wr FROM wr_reported_sites wr
    INNER JOIN sites s
    ON wr.site_name LIKE s.site_name;
Erick
  • 301
  • 3
  • 12
  • So I tried this and I am still getting a syntax error. I do not have unique ids in which to use so I would have to join on a like based on site name. Here is the error message I am getting when I try to run the above: #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 near 'wr INNER JOIN sites s ON wr.Site_Name LIKE s.Site_Name' at line 1 – doctorO20 Sep 06 '20 at 22:10
  • Have a look at my edited answer. I forgot to refer the table (wr) after the DELETE. It works now. – Erick Sep 06 '20 at 23:44
  • Hey Erick. So that worked in a way. So it actually ran but affected zero rows. So it works however I need the like to probably match on a wild card. I tried this DELETE wr FROM WR_reported_Sites wr INNER JOIN sites s ON wr.Site_Name LIKE %s.Site_Name% but that gives me a syntax error. To give more detail for example in the sites table I have a site_name of Sioux Falls, SD. But in the wr_reported_sites table I have a site_name of just Sioux Falls. So I need to delete this record from wr_reported_sites based on the fact that there is a Sioux Falls, SD in the sites table. – doctorO20 Sep 07 '20 at 03:51
  • There are many other records like this that I'm trying to delete so I have a list of site names in the wr_reported_sites that are not in the sites table – doctorO20 Sep 07 '20 at 03:52
  • To use like with a column name you have to use `CONCAT()` like so `ON wr.site_name LIKE CONCAT('%', s.site_name, '%')` Do be mindful of any wildcard characters the column values may have, eg: if `site_name = Hello%World` or `site_name = Some_Name` – Will B. Sep 15 '20 at 07:51