MySQL INSERT ...SELECT with SUBQUERY

Question

I have to do a insert query, inserting data from another table,

condition: if I have a 'codigoTipo' = 'A' , the column 'numero' should be the last 'numero' + 1

e.g.:

id | codigoTipo   |   numero 
1  |      A       |     1   
2  |      O       |     1   
3  |      A       |     2   
4  |      A       |     3

   INSERT asociados (id, codigoTipo, numero, cp, direccion, email, fax, movil, nombre, nombreEncargado,  telefono, website, idLocalidad) 
    SELECT p.id, 'A', (MAX(asociados.numero)+1 ) , p.postalcode, p.address, p.mail, p.fax, p.movil, p.name, p.charge_person, p.phone, p.website, p.locality 
    FROM 
partners as p, asociados

How can I do that? My code has an error.

Edit: codigoTipo and numero, are a compositePK thats why I need the autoincremental number in column 'numero'

If codigoTipo is 'O' or 'I', what should the value of numero be? — The Scrum Meister
– The Scrum Meister, Commented Feb 21, 2011 at 20:01

The Scrum Meister · Accepted Answer · 2011-02-21 20:05:21Z

3

You can use a subquery to get the maximum numero.

INSERT asociados (id, codigoTipo, numero, cp, direccion,
    email, fax, movil, nombre, nombreEncargado,  telefono, website, idLocalidad) 
SELECT p.id, 'A', dm.MaxNum + 1 , p.postalcode, p.address,
   p.mail, p.fax, p.movil, p.name, p.charge_person,
   p.phone, p.website, p.locality 
FROM partners as p, (SELECT MAX(numero) MaxNum FROM asociados) dm

Update: If you want the rows being inserted to increase, use this query:

INSERT asociados (id, codigoTipo, numero, cp, direccion,
    email, fax, movil, nombre, nombreEncargado,  telefono, website, idLocalidad) 
SELECT p.id, 'A', @r := @r + 1 , p.postalcode, p.address,
   p.mail, p.fax, p.movil, p.name, p.charge_person,
   p.phone, p.website, p.locality 
FROM partners as p, (SELECT @r := MAX(numero) MaxNum FROM asociados) dm

edited Feb 21, 2011 at 20:05

answered Feb 21, 2011 at 19:57

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ronnis Over a year ago

Won't dm.MaxNum + 1 have the same value for all rows?

The Scrum Meister Over a year ago

@ronnis It will, as far as i know the OP wants the same.

grteibo Over a year ago

have a error with the updated query : "Duplicate entry 'A-0' for key 2"

grteibo Over a year ago

@the-scrum-meister MAX(numero) was returning a NULL, because I don't have files yet, solved with this (SELECT @r := IFNULL(MAX(numero),0). thank you!

Collectives™ on Stack Overflow

MySQL INSERT ...SELECT with SUBQUERY

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related