Search code examples
mysqlsql-updateinner-join

MySQL Update Attribute of Table1 with Attribute of Table2

While using following SQL Statement:

UPDATE nsuppliercomponent
INNER JOIN nsuppliercomponent ON supplier.supplierName = nsuppliercomponent.supplierId
SET nsuppliercomponent.supplierId = supplier.supplierId WHERE
nsuppliercomponent.supplierId = supplier.supplierName;

I get following error message:

Error

SQL query:

UPDATE nsuppliercomponent
INNER JOIN nsuppliercomponent ON supplier.supplierName = nsuppliercomponent.supplierId
SET nsuppliercomponent.supplierId = supplier.supplierId WHERE
nsuppliercomponent.supplierId = supplier.supplierName

MySQL said: Documentation
#1066 - Not unique table/alias: 'nsuppliercomponent'

I have 2 Tables in TableA i store all the numbers from the suppliers in TableB i store the Suppliers themselves. I would like to use ForeignKeys later on why i need to change the supplierID from TableA where everything at the moment is stored as a String containing the Name to a INT with the right supplier ID

Tables as follows: TableA: enter image description here

TableB: enter image description here

Solution

  • simple typo I think, you joined the same table twice instead of the supplier table:

    UPDATE nsuppliercomponent
INNER JOIN supplier ON supplier.supplierName = nsuppliercomponent.supplierId
....