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

Databases
1

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'
2

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;
3

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
4

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 :slight_smile:

5

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