How do I insert multiple rows in MySQL using nested select statements?
November 17, 2009 - 10:29 pm
e.g. insert into lmms.household (id,family_name,location_id,last_update_time,last_update_user_id,creation_time,creation_user_id) values
((select households_id from world.households),(select family_name from world.households),"93469603",(select last_updated from world.households),"1",(select created_date from world.households),"1");
returns an error- "subquery returns more than one row"?
How can this statement be re-written?
select households_id from world.households
select family_name from world.households
select last_updated from world.households
those statements need to be 1 row.
just put a conditional like where whatever = whatever
November 18th, 2009 at 4:13 am
Could you do something like:
select * from world.households
References :
November 18th, 2009 at 4:25 am
select households_id from world.households
select family_name from world.households
select last_updated from world.households
those statements need to be 1 row.
just put a conditional like where whatever = whatever
References :
November 18th, 2009 at 4:35 am
I’ve found that when you want to turn a vertical data set ( 5 rows , each with one piece of data), into a horizontal row ( with 5 columns), you want to use a left join:
– I’ll show 3, you can retrofit to your problem
select a.value, b.value, c.value
from table a
left join table b
on b.value=<whatever b>
left join table c
on c.value=<whatever c>
where a.value=<whatever a>
Usually in left joins you care what table you start with, and then are ‘appending’ on columns from other tables based on values from the first, but in your case, you don’t care.
then just put the select inside a insert:
insert into new_table ( value1, value2, value 3)
( <above> );
Good luck.
References :