Insert Select plus curdate()

[computermax2328]

Hello All,

 

I have a data table with the columns firstname, lastname and rank. I am moving those values from that table into an archive table and the archive table has the same structure, but an additional date on the end. Obviously to tell the different between the repeating values.

 

I have a query like this......

$insert = "INSERT INTO archive (firstname, lastname, rank) VALUES (SELECT firstname, lastname, era FROM old)";

 

How can I add the mysql command CURDATE() into that insert command? Can I add parentheses inside of the parentheses around the SELECT command??

 

Thanks in advance??

[Jessica]

.

$insert = "INSERT INTO archive (firstname, lastname, rank, datefield) VALUES (SELECT firstname, lastname, era, CURDATE() FROM old)";

[Jessica]

Actually this is better sorry

 

.

$insert = "INSERT INTO archive (firstname, lastname, rank, datefield)
SELECT firstname, lastname, era, CURDATE() FROM old";

 

 

I don't think you can use values the way you were, it'll just do one row.

[computermax2328]

Thanks for getting back to me. One more quick question. I used the technique you showed me above and it worked well, but I was wondering, does it matter if the INSERT columns are not the same as the SELECT columns? The only difference is they have different names. Everything else is the same, storage type and all.

 

Thanks,

[Barand]

It doesn't matter if names differ, just the field types have to be compatible. Note that there is no matching datefield name in the query you are already using

Edited January 28, 2013 by Barand

[Jessica]

As Barand said it doesn't matter. Think about this - you can do a SELECT of something that isn't a column name. Try SELECT 'foo' FROM table. You can select a string you type, you can select CURDATE() or NOW(), you can SELECT 5-2. From a table or not sometimes. You can select them with aliases. It doesn't matter for an insert.