Insert data into table with result from another select query

mysql select insert
mmdel picture mmdel · Jun 15, 2011 · Viewed 101.5k times · Source

I am seeking help on the following issue: I have two tables Table_1 columns are itemid, locationid, quantity

Table_2 columns are itemid, location1, location2, location3

I want to copy data from Table_1 (only quantity column) into Table_2 (into location1 column). The itemid are same in both the tables(Table_1 has duplicate item id's) so that's the reason I want to copy to a new table and keep all quantity in one single row with each location as a column. I am using the below query but it doesn't work

INSERT INTO 
Table_2(location1) 
(
 SELECT qty 
 FROM Table_1 
 WHERE locationid = 1 AND Table_1.locationid = Table_2.locationid
)

Answer

Aziz Shaikh picture Aziz Shaikh · Jun 15, 2011

If table_2 is empty, then try the following insert statement:

insert into table_2 (itemid,location1) 
select itemid,quantity from table_1 where locationid=1

If table_2 already contains the itemid values, then try this update statement:

update table_2 set location1=
(select quantity from table_1 where locationid=1 and table_1.itemid = table_2.itemid)

Related questions

MySQL INSERT INTO ... VALUES and SELECT

Is there a way to insert pre-set values and values I get from a select-query? For example: INSERT INTO table1 VALUES ("A string", 5, [int]). I have the value of "A string" and the number 5, but I've to find the [int] …

Read More
MySQL error 1241: Operand should contain 1 column(s)

I am trying to Insert data from a table1 into table2 insert into table2(Name,Subject,student_id,result) select (Name,Subject,student_id,result) from table1; Key for table2 is student_id. Assume that there are not any duplicates. …

Read More
Copy rows from one table onto another using INSERT query

I have a table with 158 columns and 22,000 rows and I have another empty table where I want insert values dynamically based on the WHERE condition coming from the user. The SELECT query will look something like this: SELECT * FROM mygrist_…

Read More