mysql: update with subquery,

Question

I have an update query with a select statement, which separately works. It's using it for the update that's not working.

update data set data.id = (select nid from node inner join data on node.title = data.name);

I get the error

"You can't specify target table 'data' for update in FROM clause"

So, after digging around, I found that I could write include another select statement:

update data set data.id = (select nid from(select nid from node inner join data on node.title = data.name) AS temptable);

I get the error

"Subquery returns more than 1 row "

So after more digging, I added an "ANY", as this is the common recommendation:

update data set data.id = (select nid from ANY (select nid from node inner join data on node.title = data.name) AS temptable);

and get

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select nid from node inner join data on node.title = biblio_' at line 1 "

What am I missing?

Answer 1

If we want to update all rows in table data table, we can do something like this:

UPDATE data
  LEFT
  JOIN node
    ON node.title = data.name
   SET data.id = node.nid

NOTES:

If there are two or more rows in node that have the same value for title (which match name in data), the value of nid from one of the matching rows will be assigned, but it's indeterminate which of those row.

If there are values of name in the data table which are not found in the node table (in the title column), then a NULL value will be assigned to the id column.

Some tweaks to the query can modify this behavior.

We can achieve a similar result using a correlated subquery. Here's an example:

UPDATE data
   SET data.id = ( SELECT node.nid
                     FROM node
                    WHERE node.title = data.name
                    ORDER BY node.nid
                    LIMIT 1
                 )