1

First time posting so forgive me if I omit anything. I have been struggling to Transfer data from one table to another in MySQL Community Server - GPL Version: 8.0.11. To better set the scene I import data from a MongoDB and then perform the STR_TO_DATE function and then need to move specific columns to another table one of which is one of the DATETIME data typed columns whenever I run the command/query. Here is the code :

INSERT INTO mlb.hazards 
SELECT '_id', 'Source', 'CreatedTime', 'AlarmId', 'Description'
FROM mlb.incident_20181007 
WHERE Description = 'ALARM ON Runway Hazard 27R 9L';

I get the return error message:

Error Code: 1292. Incorrect datetime value: 'CreatedTime' for column 'CreatedTime' at row 120 0.000 sec

I dug up the data for CreatedTime and _id for row 120 and this is what I found.

Data copied from Mysql workbench: '2018-07-03 13:35:41.421000', '621E30C4401749328035E215E680CA6A'

The columns data types are listed as DATETIME in both tables, please help me understand what I'm missing here. Thank you in advance.

Ivam
  • 379
  • 3
  • 12
R. Kennon
  • 11
  • 2

1 Answers1

0

This is probably a simple issue of you passing a string instead of a column value. It looks like you're trying to insert the values of columns from your incident table (eg column _id, Source, etc). However, because you have then wrapped in single quotes, you are actually passing the literal string value. In other words, it's trying to insert the string "CreatedTime" instead of the datetime value that is in that column.

Instead of single quotes, mysql uses backticks. Your query should be:

INSERT INTO mlb.hazards 
SELECT 
    `_id`, `Source`, `CreatedTime`, `AlarmId`, `Description` 
FROM 
    mlb.incident_20181007 
WHERE 
    `Description` = 'ALARM ON Runway Hazard 27R 9L';
RToyo
  • 2,877
  • 1
  • 15
  • 22
  • backticks are optional in MySQL.. – Raymond Nijland Jul 11 '18 at 15:52
  • Great this worked, I just thought they were inter changeable. Out of curiosity why would it take 120 rows to throw the error? – R. Kennon Jul 11 '18 at 16:16
  • @R.Kennon I haven't worked with MySQL in years, so I'm not sure why it referred to row 120. I'm surprised it even gives that information in the output. But it might be referring to row 120 in your table (using the default sort...Which would make it a fairly useless error message). If you just do `select Description from mlb.incident_20181007 limit 121`, is the 120th row the only one that matches the Description in your where clause? – RToyo Jul 11 '18 at 18:37