1248 - Every derived table must have its own alias

Databases
1

Hello guys, I need your appreciated help.
I need ectract the date and the hour in this string:

[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

1248 - Every derived table must have its own alias.

The query select returns the correct string:

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.

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

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

3

Thank you very much, you have right …:slight_smile:

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
		) [B]AS tmp0[/B]
) AS tmp ON tmp.trace = u.trace