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>