2

This is a bit different than the normal "You can't specify target table for update in FROM clause" questions. I know I need to create a temp table as part of the query to get around that, but I'm doing that as shown below and still getting that error.

LOAD DATA LOCAL INFILE 'filename.csv' 
INTO TABLE campaigner.areas 
CHARACTER SET latin1 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES 
(abbr, fips_code, name) 
SET area_type_id    = @STATE_TYPE_ID,
 description     = NULL,
 gnis_feature_id = NULL,
 parent_id       = (SELECT c.country_id FROM (SELECT country_id,country_abbr FROM campaigner.countries_v) AS c WHERE c.country_abbr = 'US'),
 latitude        = NULL,
 longitude       = NULL;

Here's the definition of countries_v, which, as you can see, does use the areas table.

CREATE OR REPLACE VIEW campaigner.countries_v (country_id, country_abbr, country_name, fips_code) AS
            SELECT c.country_id, ca.abbr, ca.name, ca.fips_code
              FROM campaigner.countries c
         LEFT JOIN campaigner.areas     ca  ON c.country_id  = ca.area_id;

What am I missing?

Gary Kephart
  • 4,860
  • 5
  • 39
  • 52
  • Update: This works in MySQL 5.6 – Gary Kephart Jan 24 '18 at 17:27
  • And I found a comment in another page that I think explains why it works in 5.6 and not in 5.7: https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause "However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks." – Gary Kephart Jan 24 '18 at 17:44

2 Answers2

2

You are inserting data into the table campaigner.areas wich you also use in the view, thats the problem.

justcode
  • 1,562
  • 3
  • 14
  • 25
1

And I found a comment in another page that I think explains why it works in 5.6 and not in 5.7: stackoverflow.com/questions/45494/… "However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks." So I am using SET optimizer_switch = 'derived_merge=off';

Gary Kephart
  • 4,860
  • 5
  • 39
  • 52
  • Still having problems. I've tried setting the optimizer_switch, using DISTINCT and LIMIT in the subquery, to no avail. Could it be that being part of a LOAD DATA LOCAL INFILE statement is causing all these things to be ignored? – Gary Kephart Jan 26 '18 at 02:20