0

If i have a select query that will always return 2-3 rows like so

id  set currStatus    idNew     value
20  1       4           3       15.00
20  2       4           1       180.00
20  3       0           5       360.00

How can i use this data to create an insert statement for each row.

EDIT - I also need the idNew values for another update statement after.

Here is my normal insert statement. Fields that start with an underscore are params coming from the stored procedure call.

INSERT INTO DisableHistory
(idCol, idNewCol, setCol, disabled_On_Date, disabled_Off_Date, disableNote, disablingUser, recallTime)
values
(id, idNew, set, NOW(), _disableOffDate, _disableNote, _disableUser, value);

So ideally the query would look like so if it was hardcoded

INSERT INTO DisableHistory
(idCol, idNewCol, setCol, disabled_On_Date, disabled_Off_Date, disableNote, disablingUser, recallTime)
values
(20, 3, 1, NOW(), _disableOffDate, _disableNote, _disableUser, 15.00),
(20, 1, 2, NOW(), _disableOffDate, _disableNote, _disableUser, 180.00),
(20, 5, 3, NOW(), _disableOffDate, _disableNote, _disableUser, 360.00);
Eric G
  • 928
  • 1
  • 9
  • 29
  • 1
    you can use "INSERT ... SELECT" https://dev.mysql.com/doc/refman/5.7/en/insert-select.html – Tamar Jun 14 '17 at 13:35
  • But there are fields not from the select statement that have to be part of the insert statement, wouldnt insert select require that the select columns equals the insert columns? – Eric G Jun 14 '17 at 13:38
  • 1
    yes. like :`insert into DisplayHistory select id, idNew, set,now().....` – Tamar Jun 14 '17 at 13:39
  • I forgot to mention but I also need the `idNew` values for another update statement after. – Eric G Jun 14 '17 at 13:45

0 Answers0