Insert values from one table to another by query

Question

I have two tables Like Below

CREATE TABLE projectlist(ProjectId   INT NOT NULL PRIMARY KEY,
                         ProjectName VARCHAR(50),
                         Location    VARCHAR(50));

CREATE TABLE LocationList(LocaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                          ProjectId INT,
                          Location    VARCHAR(50));

The Values in the table are as Below

INSERT INTO projectlist(ProjectId, ProjectName)
                 VALUES(1, 'Project A'),
                       (2, 'Project B'),
                       (3, 'Project C'),
                       (4, 'Project D'),
                       (5, 'Project E'),
                       (6, 'Project F'),
                       (7, 'Project G'),
                       (8, 'Project H');

INSERT INTO LocationList(ProjectId, Location)
                  VALUES(1, 'Location A'),
                        (1, 'Location C'),
                        (2, 'Location C'),
                        (2, 'Location B'),
                        (2, 'Location A'),
                        (3, 'Location B'),
                        (4, 'Location C'),
                        (5, 'Location D'),
                        (6, 'Location A'),
                        (6, 'Location B'),
                        (7, 'Location B'),
                        (8, 'Location D'),
                        (8, 'Location A');

I want a Insert Query which inserts First LocationList.Location into projectlist.Location for their respective project relating on project id.

So the table projectlist after running query is

   ProjectName                 Location
    Project A                 Location A 
    Project B                 Location C
    Project C                 Location B
    Project D                 Location C
    Project E                 Location D
    Project F                 Location A
    Project G                 Location B
    Project H                 Location D

I tried a SQL query which bring the First Location from LocationList table as below.

 SELECT DISTINCT MNPCL.Location, MNP.ProjectId
   FROM LocationList MNPCL RIGHT OUTER JOIN
        projectlist MNP ON MNP.ProjectId = MNPCL.ProjectId                            
  GROUP BY MNP.ProjectName

How to write a query which perform insert in the projectlist Location

Thanks For Reply

Try getting a help from this question http://stackoverflow.com/questions/6846485/insert-data-from-one-table-to-another — Ankush, Dec 14 '12 at 07:58

score 1 · Accepted Answer · answered Dec 14 '12 at 08:16

Here is a SQLFiddle demo

update projectlist 
join 
(
select LocationList.ProjectID,LocationList.Location 
  from LocationList
join 
(select LocationList.ProjectID,min(LocaId) minLocaId 
    from LocationList 
    group by LocationList.ProjectID) l1
        on LocationList.LocaId=l1.minLocaID
 ) l2 on projectList.ProjectID=l2.ProjectID
SET ProjectList.Location=l2.Location

score 1 · Answer 2 · answered Dec 14 '12 at 08:30

try:

UPDATE projectlist AS prj SET Location = 
(SELECT Location FROM LocationList AS lst WHERE lst.ProjectId = prjProjectId ORDER BY Location DESC LIMIT 1)

since your projects have more than one location in LocationList and you can only have one location in your projectlist table you have to limit the result set to one row.

score 0 · Answer 3 · answered Dec 14 '12 at 09:17

0

This will work for you...

UPDATE projectlist AS prj SET Location = (
SELECT group_concat(locationlist.Location SEPARATOR ',') as loc FROM `locationlist` left join projectlist using (ProjectId) 
group by locationlist.ProjectId)

answered Dec 14 '12 at 09:17

Suresh Kamrushi

15,627
13
75
90

Insert values from one table to another by query

3 Answers3

Here is a SQLFiddle demo