Re^3: Perl/MySQL table data copy

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 :

mysql> select * from coordsys;
+-------------+--------------------------------+
| ID_COORDSYS | COORDSYS_NAME                  |
+-------------+--------------------------------+
|           1 | Solar Disc                     |
|           2 | Heliographic                   |
|           3 | Heliocentric Ecliptic          |
|           4 | Geocentric Inertial            |
|           5 | Geocentric Solar Magnetosferic |
|           6 | Galactic                       |
|           7 | SOHOcentric Ecliptic           |
|          10 | SDC                            |
|          11 | Solar Disc Polar               |
|          12 | N/A                            |
|          13 | Solar Cropped                  |
+-------------+--------------------------------+
11 rows in set (0.02 sec)

mysql> select 'test' from coordsys;
+------+
| test |
+------+
| test |
| test |
| test |
| test |
| test |
| test |
| test |
| test |
| test |
| test |
| test |
+------+
11 rows in set (0.00 sec)

mysql> select * from coordsys;
+-------------+--------------------------------+
| ID_COORDSYS | COORDSYS_NAME                  |
+-------------+--------------------------------+
|           1 | Solar Disc                     |
|           2 | Heliographic                   |
|           3 | Heliocentric Ecliptic          |
|           4 | Geocentric Inertial            |
|           5 | Geocentric Solar Magnetosferic |
|           6 | Galactic                       |
|           7 | SOHOcentric Ecliptic           |
|          10 | SDC                            |
|          11 | Solar Disc Polar               |
|          12 | N/A                            |
|          13 | Solar Cropped                  |
+-------------+--------------------------------+
11 rows in set (0.00 sec)

mysql> select *,'test' from coordsys;
+-------------+--------------------------------+------+
| 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                  | test |
+-------------+--------------------------------+------+
11 rows in set (0.00 sec)

mysql> select 'test', * from coordsys;
ERROR 1064 (00000): You have an error in your SQL syntax.  Check the m
+anual that corresponds to your MySQL server version for the right syn
+tax to use near '* from coordsys' at line 1
[download]

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)

mysql> create table temp_table_name ( test varchar(4) );
Query OK, 0 rows affected (0.00 sec)

mysql> select temp_table_name.*, coordsys.* from coordsys left outer j
+oin temp_table_name on temp_table_name.test = coordsys.id_coordsys;
+------+-------------+--------------------------------+
| test | ID_COORDSYS | COORDSYS_NAME                  |
+------+-------------+--------------------------------+
| NULL |           1 | Solar Disc                     |
| NULL |           2 | Heliographic                   |
| NULL |           3 | Heliocentric Ecliptic          |
| NULL |           4 | Geocentric Inertial            |
| NULL |           5 | Geocentric Solar Magnetosferic |
| NULL |           6 | Galactic                       |
| NULL |           7 | SOHOcentric Ecliptic           |
| NULL |          10 | SDC                            |
| NULL |          11 | Solar Disc Polar               |
| NULL |          12 | N/A                            |
| NULL |          13 | Solar Cropped                  |
+------+-------------+--------------------------------+
11 rows in set (0.00 sec)
[download]

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]

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