Loop sql query with insert

Question

I have this select query:

SELECT * FROM `jos_users` 
left join jos_clientes on jos_users.id = jos_clientes.id_user 
where jos_clientes.id_user is null 
and email like '%novousuario%'';

This query returns me all jos_users that have not a row on the jos_clientes table.

The jos_clientes schema is:

create table jos_clientes(
  id int(11) not null auto_increment primary key,
  id_user int(11) not null,
  codigo_cpf_cnpj varchar(20) not null,
  type varchar(4) not null
);

Is there any way to, for each row, I insert a new row in jos_clientes?

foreach jos_users
    INSERT INTO jos_clientes VALUES (null, jos_users.id_user, jos_users.username, IF(length(jos_users.username)>11,'cnpj','cpf'));

How can I do this with sql on mysql?

tadman · Accepted Answer · 2014-09-12 19:11:49Z

2

You can use the INSERT INTO ... SELECT FROM approach:

INSERT INTO jos_clientes (id_user, username, code)
  SELECT jos_users.id_user, jos_users.username, IF(length(jos_users.username)>11,'cnpj','cpf') FROM jos_users

You need to be specific about the fields you're populating though unless the columns match exactly.

Adjust the SELECT sub-statement according to your needs and be sure it produces the kind of results you can insert directly into your jos_clientes table.

answered Sep 12, 2014 at 19:11

tadman

212k23 gold badges237 silver badges266 bronze badges

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

Comments

Akhil · Accepted Answer · 2014-09-12 19:17:47Z

0

Try the following query:

 INSERT INTO jos_clientes SELECT null,d.id_user, d.username,IF(length(d.username)>11,'cnpj','cpf'))
    FROM jos_users d;

answered Sep 12, 2014 at 19:17

Akhil

4793 silver badges13 bronze badges

Collectives™ on Stack Overflow

Loop sql query with insert

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related