CODEWITHSUNDEEP
mysqlsqlmariadb
Mekroebo
Mekroebo

Reputation: 461

Update MySql Table with information from another and same table

We have a couple of tables in out database. First we have the Extensions table which holds (among other things) the CustomerId and the ExtensionId

In addition we have a table called ExtensionCallCenters which currently only holds two columns: ExtensionId and CallCenterId

I've been tasked with adding a column to this table for the CustomerId. I can get the CustomerId from the Extensions table with a simple LEFT JOIN statement:

SELECT Extensions.CustomerId 
FROM ExtensionCallCenters 
LEFT JOIN Extensions ON Extensions.ExtensionId = ExtensionCallCenters.ExtensionId

But I cannot update the table with this information. I've already read through a couple of threads here on SO so I already know that I need to do something with an INNER JOIN.

I've tried this:

UPDATE ExtensionCallCenters AS t1 
INNER JOIN ExtensionCallCenters AS t2 
SET t1.CustomerId = ( SELECT Extensions.CustomerId 
                      FROM t2 
                      LEFT JOIN Extensions ON Extensions.ExtensionId = ExtensionCallCenters.ExtensionId
                     )

But this gives me an error that t2 does not exist. If I change the t2 in the subquery to the original ExtensionCallCenters then MariaDB tells me that I cannot update the table with the same table.

Does anybody have an idea how to resolve this?

Upvotes: 0

Views: 34

Answers (1)

Mekroebo
Mekroebo

Reputation: 461

Found the answer but not quite sure how this working. For anyone who is looking for the same answer: UPDATE ExtensionCallCenters AS t1 INNER JOIN ExtensionCallCenters AS t2 SET t1.CustomerId = (SELECT CustomerId FROM Extensions WHERE ExtensionId = t1.Extension)

Upvotes: 1

Related Questions