Update with MySQL subquery,

Question

Update with MySQL subquery,

Navigation

#1 by (1 votes)

0

I have a project where I have to do a update of a table field (active) , I practically have something like this:

UPDATE 
t1 
INNER JOIN 
(SELECT 
  MAX(id) 
FROM
  t2_copy 
GROUP BY idq
ORDER BY idq DESC)AS x2
SET activa = TRUE 
WHERE x2.id

I copied the table to create a subquery. from it and do a inner join to include it and where the id_max is equal to the id update

But I get an error that you do not know x2.id for example as if you do not accept the alias.

mysql

asked by Monster 22.11.2017 в 20:13

source

1 answer

Operation of form when changing it from input to select [closed] Arrangement of JQuery Dates

score 1 · Accepted Answer

Since you did not assign an alias to the value returned in your derived table, MySQL automatically assigns a name corresponding to the expression in SELECT . That is, in this case, the name of the column is literally MAX(id) .

That is, you could theoretically reference the column using:

WHERE x2.'MAX(id)' = ...

Of course, that looks a bit ridiculous and it's not practical. So the best thing is that you assign the name you want with the clause AS :

SELECT MAX(id) as id

... so you can refer to it in the expected way:

WHERE x2.id = ...