Re^2: Perl/MySQL table data copy

Replies are listed 'Best First'.
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]