website stat

INSERT INTO…SELECT on MySQL

I've been submerged in random ad-hoc things that need to be done so I don't even have the time to keep up with the blogging community or even to blog myself. Anyway, just a small note to the fact that if you're trying to use INSERT INTO...SELECT on MySQL 5.0 be warned that you can't mix data values with fields, ie, you can't do this:

PLAIN TEXT
SQL:
  1. INSERT INTO TABLE (1, field_id)
  2. SELECT FIELD.id AS field_id FROM table2

You'll have to replace that with a variable like you do with "field_id". I don't know why this is so, but it is.


7 Responses to “INSERT INTO…SELECT on MySQL”

Feed for this Entry
  1. Vitor
    Published at October 2nd, 2007 at 4:07 am

    Is that even a valid SQL statement? You cannot have values after the table name, but just the column names.

    Why don’t you do something like:
    insert into table (col1, col2) select 1, field.id from table 2

    At least this is standard SQL. Don’t have MySQL installed to try it.

  2. mlopes
    Published at October 2nd, 2007 at 2:01 pm

    That’s also a valid option. Thanks Vitor.

  3. Kurt Junshean Espinosa
    Published at October 8th, 2007 at 6:50 pm

    hi,

    Perhaps, u’ve got some idea why there is an error here. I cannot seem to figure it out.

    insert into integrated(dist2uc,dist2hw) select d.dist2uc, d.dist2hw from dist2unh d where d1.pin=d.pin;
    ERROR 1054 (42S22): Unknown column ‘d1.pin’ in ‘where clause’

    Thanks.

  4. mlopes
    Published at October 8th, 2007 at 7:47 pm

    I guess you mean

    insert into integrated(dist2uc,dist2hw) select d.dist2uc, d.dist2hw from dist2unh, d, d1 where d1.pin=d.pin;

  5. Kurt Junshean Espinosa
    Published at October 9th, 2007 at 5:23 am

    Thanks much. I made a workaround because my intention was from many tables. I made a script to integrate them all according to their common PINs. What I did was this:

    insert into i1(pin,dist2uc, dist2hw) select e.pin, d.dist2uc,d.dist2hw from d1 e, dist2unh d where e.pin = d.pin;

    insert into i2(pin,dist2uc, dist2hw, pctagr, pctbar, pctfor,pcturb,pctwtr,pctwet) select i1.pin,i1.dist2uc, i1.dist2hw, n.pctagr, n.pctbar,n.pctfor, n.pcturb,n.pctwtr,n.pctwet from i1, neighbor n where i1.pin=n.pin;

    insert into i3(pin,dist2uc, dist2hw, pctagr, pctbar, pctfor,pcturb,pctwtr,pctwet, mean) select i.pin,i.dist2uc, i.dist2hw, i.pctagr, i.pctbar,i.pctfor, i.pcturb,i.pctwtr,i.pctwet, s.mean from i2 i , slope s where i.pin=s.pin;

    insert into i4(pin,dist2uc, dist2hw, pctagr, pctbar, pctfor,pcturb,pctwtr,pctwet, mean, musym, area) select i.pin,i.dist2uc, i.dist2hw, i.pctagr, i.pctbar,i.pctfor, i.pcturb,i.pctwtr,i.pctwet, i.mean, o.musym, o.area from i3 i , soilsdesigntn o where i.pin=o.pin;

    insert into i5(pin,dist2uc, dist2hw, pctagr, pctbar, pctfor,pcturb,pctwtr,pctwet, mean, musym, area, landuse) select i.pin,i.dist2uc, i.dist2hw, i.pctagr, i.pctbar,i.pctfor, i.pcturb,i.pctwtr,i.pctwet, i.mean, i.musym, i.area, l.landuse from i4 i , landusetype l where i.pin=l.pin;

    In short, I just performed a join between two tables at a time until I got the complete table which “i5″ holds.

  6. Kurt Junshean Espinosa
    Published at October 9th, 2007 at 5:24 am

    Correction above: “rfom many tables I should get one table that contains all fields.”

  7. Kurt Junshean Espinosa
    Published at October 9th, 2007 at 5:25 am

    Correction above: “from many tables I should arrive to one table that contains all fields.”