Why get I MySQL error 1066 on inner join?

Question

Why am I getting a Error Code 1066. Not unique table/alias usps 75078 on this sql script:

UPDATE `20160318 flagdatabase`,
       `usps 75078`
INNER JOIN `usps 75078` ON `20160318 flagdatabase.Street` = `usps 75078.Street_Name`
SET `20160318 flagdatabase.Carrier_Route` = `usps 75078.Carrier_Route`
SET `20160318 flagdatabase.Zip4_Low_Add` = `usps 75078.Zip4_Low_Add`
SET `20160318 flagdatabase.Zip4_High_Add` = `usps 75078.Zip4_High_Address`
WHERE `20160318 flagdatabase.Street` = `usps 75078.Street_Name`
  AND `20160318 flagdatabase.HouseNumber` >= `usps 75078.Primary_Low_Address`
  AND `20160318 flagdatabase.HouseNumber` <= `usps 75078.Primary_High_Address`;

Looks like when JOINing a table to itself, you need to use two unique identifiers. See [this answer](http://stackoverflow.com/a/1435186/864233) — romellem, Mar 18 '16 at 20:02
is `20160318` the database? if so then should it be: `20160318.flagdatabase`? You don't actually have spaces in your column / table names do you? That is going to make it interesting to read and maintain? Please add the table create statements to your question so I can understand what is really happening? — Ryan Vincent, Mar 20 '16 at 21:15

score 1 · Answer 1 · answered Mar 20 '16 at 22:56

Your query is very hard to read due to the (insane) use of dots and spaces in table names, so I'll paraphrase using placeholder names.

Your query looks like:

update t1, t2 join t2 on some condition
...

which lists t2 twice in the query. You must give (at least) one of the t2 references an alias, and use that alias instead of the table name throughout the query, like this:

update t1, t2 join t2 as t3 on some condition
...

Here I've used "t3" as the alias.

score 0 · Answer 2 · answered Mar 20 '16 at 21:14

I think you need to fix your query, and leave it like this:

UPDATE `20160318 flagdatabase`,
INNER JOIN `usps 75078` ON `20160318 flagdatabase.Street` = `usps 75078.Street_Name`
SET `20160318 flagdatabase.Carrier_Route` = `usps 75078.Carrier_Route`, 
`20160318 flagdatabase.Zip4_Low_Add` = `usps 75078.Zip4_Low_Add`.
`20160318 flagdatabase.Zip4_High_Add` = `usps 75078.Zip4_High_Address`
WHERE `20160318 flagdatabase.Street` = `usps 75078.Street_Name`
  AND `20160318 flagdatabase.HouseNumber` >= `usps 75078.Primary_Low_Address`
  AND `20160318 flagdatabase.HouseNumber` <= `usps 75078.Primary_High_Address`;

Why get I MySQL error 1066 on inner join?

2 Answers2