SitePoint Sponsor

User Tag List

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

    1248 - Every derived table must have its own alias

    Hello guys, I need your appreciated help.
    I need ectract the date and the hour in this string:
    Code:
    [1370940811 06/11/2013 10:53:31 AM]: New message created in database
    And update the field date_made of my table tbl_tamper with the value date/time for this string.
    I tried this solution, but I've the error
    Code:
    1248 - Every derived table must have its own alias.
    The query select returns the correct string:
    Code:
    mysql> SELECT
    	CONCAT(
    		theDate,
    		' ',
    		theHour
    	) AS date_made
    FROM
    	(
    		SELECT
    			STR_TO_DATE(
    				SUBSTRING_INDEX(
    					SUBSTRING_INDEX(
    						'[1370940811 06/11/2013 10:53:31 AM]: New message created in databas',
    						' ',
    						2
    					),
    					' ',
    					- 1
    				),
    				'%m/%d/%Y'
    			) AS theDate,
    			SUBSTRING_INDEX(
    				SUBSTRING_INDEX(
    					'[1370940811 06/11/2013 10:53:31 AM]: New message created in databas',
    					' ',
    					3
    				),
    				' ',
    				- 1
    			) AS theHour
    	) AS tmp;
    +---------------------+
    | date_made           |
    +---------------------+
    | 2013-06-11 10:53:31 |
    +---------------------+
    1 row in set
    
    mysql>
    Can you help me?
    Thank you in advance.
    Code:
    mysql> UPDATE tbl_tamper AS u
    JOIN (
    	SELECT
    		CONCAT(
    			myDate,
    			' ',
    			theHour
    		) AS date_made
    	FROM
    		(
    			SELECT
    				STR_TO_DATE(
    					SUBSTRING_INDEX(
    						SUBSTRING_INDEX(
    							trace,
    							' ',
    							2
    						),
    						' ',
    						- 1
    					),
    					'%m/%d/%Y'
    				) AS myDate,
    				SUBSTRING_INDEX(
    					SUBSTRING_INDEX(
    						trace,
    						' ',
    						3
    					),
    					' ',
    					- 1
    				) AS theHour
    			FROM
    				tbl_tamper
    		)
    ) AS tmp ON tmp.`STATUS` = `u.type`
    SET u.date_made = tmp.date_made;
    1248 - Every derived table must have its own alias
    mysql>

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you know what a derived table is, right? it's a subquery used in the FROM clause

    one of your derived tables is missing its table alias

    look hard, and you'll see which one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you know what a derived table is, right? it's a subquery used in the FROM clause

    one of your derived tables is missing its table alias

    look hard, and you'll see which one
    Thank you very much, you have right ...
    Code:
    mysql> UPDATE tbl_tamper AS u
    JOIN (
    	SELECT
    		CONCAT(
    			myDate,
    			' ',
    			theHour
    		) AS date_made, trace
    	FROM
    		(
    			SELECT
    				STR_TO_DATE(
    					SUBSTRING_INDEX(
    						SUBSTRING_INDEX(
    							trace,
    							' ',
    							2
    						),
    						' ',
    						- 1
    					),
    					'%m/%d/%Y'
    				) AS myDate,
    				SUBSTRING_INDEX(
    					SUBSTRING_INDEX(
    						trace,
    						' ',
    						3
    					),
    					' ',
    					- 1
    				) AS theHour, trace
    			FROM
    				tbl_tamper
    		) AS tmp0
    ) AS tmp ON tmp.trace = u.trace


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
  •