Unknown column 'q.name' in 'where clause'

[Miguel61]

Hi all, I need your important help.

Why this query is wrong?

The column `name` is selected in the query or not ?

Many thanks

mysql> UPDATE `tb1` t
JOIN(

		SELECT
			name,
			DATEDIFF(CURRENT_DATE(), `maxdata`) `dateDiff`
		FROM
	(
				SELECT
					name,
					MAX(`myDates`)`maxdata`
				FROM
					tb2
				GROUP BY
					name
				ORDER BY
					`maxdata` ASC

	)q
)x
SET t.GG = q.`dateDiff`
WHERE
   t.name = q.name;
1054 - Unknown column 'q.name' in 'where clause'

[goldkiller07]

I've never seen a query done like this but try -

mysql> UPDATE `tb1` AS t
JOIN(
 
		SELECT
			name,
			DATEDIFF(CURRENT_DATE(), `maxdata`) `dateDiff`
		FROM
	(
				SELECT
					name,
					MAX(`myDates`)`maxdata`
				FROM
					tb2
				GROUP BY
					name
				ORDER BY
					`maxdata` ASC
 
	) AS q
)x
SET t.GG = q.`dateDiff`
WHERE
   t.name = q.name;

[Miguel61]

thanks I find my error, now working :

mysql> UPDATE `tb1` t
JOIN(
	SELECT
		name,
		DATEDIFF(CURRENT_DATE(), `maxdata`) `dateDiff`
	FROM
		(
			SELECT
				name,
				MAX(`myDates`)`maxdata`
			FROM
				`tb2`
			GROUP BY
				name
			ORDER BY
				`maxdata` ASC
		)q
)x
SET t.GG = x.`dateDiff`
WHERE
	1
AND t.name = x.name;
Query OK, 100 rows affected
Rows matched: 162  Changed: 0  Warnings: 0

[r937]

simpler and neater --

UPDATE tb1 AS t
INNER
  JOIN ( SELECT name
              , DATEDIFF(CURRENT_DATE,MAX(mydates)) AS `dateDiff`
           FROM tb2
         GROUP 
             BY name ) AS x
    ON x.name = t.name          
   SET t.GG = x.`dateDiff`

note to both -- if you're gonna use JOIN, then you gotta use ON

[Miguel61]

you're right really: `simpler and neater`
thanks a lot and good weekend my teacher !!!!