CODEWITHSUNDEEP
mysqlsql
ArrayOutOfBound
ArrayOutOfBound

Reputation: 2618

Insert values from one table to another by query

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

Upvotes: 1

Views: 526

Answers (3)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16076

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)

Upvotes: 0

Euclides Mulémbwè
Euclides Mulémbwè

Reputation: 1687

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.

Upvotes: 1

valex
valex

Reputation: 24134

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

Upvotes: 1

Related Questions