The first Location from second table should be added to the first table Location.

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

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

Query 2

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;
Posted in SQL.

Leave a reply