Rocco The Taco Rocco The Taco - 1 month ago 5
MySQL Question

MYSQL UPDATE SELECT from Two Tables

Using the selected answer here, I attempted to craft a MySQL query that select columns and set values but am getting a 'Every derived table must have its own alias'. I only have two tables: matrix_swfl_res & RLN which I've defined as e & d respectively. What am I missing?

UPDATE (SELECT e.MLSNumber, d.MLSNumber
FROM matrix_swfl_res e, RLN d
WHERE e.MLSNumber = d.MLSNumber)
SET e.RSLN = d.RSLN

Answer

If you consider the answer linked, you have to do a Join when you want to update a table

Here the mysql error is because

(SELECT e.MLSNumber, d.MLSNumber
          FROM matrix_swfl_res e, RLN d
         WHERE e.MLSNumber = d.MLSNumber)

is considerated as a derivated table, as you write it, it's like you want to update this derivated table.

If I understand what you want : You want to update the table A with some select you have done previously, here is what you need to do :

UPDATE A 
 INNER JOIN (SELECT e.MLSNumber, d.RSLN
              FROM matrix_swfl_res e
                   INNER JOIN RLN d ON e.MLSNumber = d.MLSNumber ) as Q 
    ON A.MLSNumber= Q.MLSNumber)
SET A.RSLN = Q.RSLN 

I don't have all the tables details, but I hope with this example it will be clearer

Comments