Re: Perl/MySQL table data copy

You're very close:

INSERT INTO `$db`.`$table`
VALUES ( SELECT  '', 'author', '',* FROM `$db`.`$oldtable` )
[download]

Of course, I assume those values you mentioned were good to go in, and wouldn't cause it to clear or override defaults in the table (if it would, then you'll want to specify which fields you're inserting into by name, which takes a bit more work).

Mysql also specifically has a INSERT...SELECTsyntax that's supposed to correctly handle autonumber fields:

INSERT INTO `$db`.`$table`
SELECT  '', 'author', '',* FROM `$db`.`$oldtable`
[download]

Update: dragonchild is right. I need to stop giving Oracle answers for mysql questions. (blah...lack of subqueries)

Comment on Re: Perl/MySQL table data copy Select or Download Code

Replies are listed 'Best First'.
Re^2: Perl/MySQL table data copy by dragonchild (Archbishop) on Mar 31, 2005 at 13:25 UTC
Your second answer is the one OP is looking for, and it has nothing to do with autonumber fields; it's the correct way to build the set from the old table that should be built for each row going into the new table. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^2: Perl/MySQL table data copy by rsiedl (Friar) on Mar 31, 2005 at 14:46 UTC
thanks for the help, however when i try SELECT 'test', * FROM `$db`.`$oldtable` [download] I get You have an error in your SQL syntax near '* FROM `test`.`test_table +` at line 1 [download] Could it be dependant upon the version of mysql? Cheers, Reagen	[reply] [d/l] [select]
Re^3: Perl/MySQL table data copy by bpphillips (Friar) on Mar 31, 2005 at 16:14 UTC
you won't get the error if you preface the * with the table name (or alias). For instance: SELECT 'test', `$db`.`$oldtable`.* FROM `$db`.`$oldtable` [download] works. However, MySQL warns not to rely on column order when selecting data with `select ` So you should be able to do: INSERT INTO `$db`.`$table` SELECT '', 'author', '', `$db`.`$oldtable`. FROM `$db`.`$oldtable` [download]	[reply] [d/l] [select]
Re^3: Perl/MySQL table data copy by jhourcle (Prior) on Mar 31, 2005 at 15:39 UTC
Well, this is what I get for not testing things first ... it seems that mysql really doesn't like that syntax, using Mysql 4.0.20 : Read more... (3 kB) I'm shocked, myself, that it doesn't let you put a constant first, but it does last. You can either give the entire list of fields for the insert, or we can cheat a few other ways. If you can create a temporary table (but you'll only get nulls in the new fields ... I don't know of an equivalent to oracle's `nvl()` in mysql) Read more... (1368 Bytes) As we're quickly out of the realm of Perl, even DBI, you might want to try on a more mysql-specific forum, as they'd be more likely to know what the inner tricks are for doing this sort of thing, and there may be some other easy way to get what you want done. And just to check that I wasn't crazy, the following works in Oracle: `SQL> select 'test',coordsys.* from soho.coordsys; 'TES ID_COORDSYS COORDSYS_NAME ---- ----------- -------------------------------------------------- test 1 Solar Disc test 2 Heliographic test 3 Heliocentric Ecliptic test 4 Geocentric Inertial test 5 Geocentric Solar Magnetosferic test 6 Galactic test 7 SOHOcentric Ecliptic test 10 SDC test 11 Solar Disc Polar test 12 N/A test 13 Solar Cropped 11 rows selected.` [download]	[reply] [d/l] [select]
Re^4: Perl/MySQL table data copy by bpphillips (Friar) on Mar 31, 2005 at 16:26 UTC
MySQL offers `ifnull()` which I'm pretty sure is equivalent to Oracle's `nvl()` function	[reply] [d/l] [select]