SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    Hi all, I need your important help.

    Why this query is wrong?

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

    Many thanks

    Code:
    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. #2
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've never seen a query done like this but try -

    Code MySQL:
    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. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thanks I find my error, now working :
    Code:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    simpler and neater --
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    you're right really: `simpler and neater`
    thanks a lot and good weekend my teacher !!!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •