CODEWITHSUNDEEP
sqlsql-servert-sqlone-to-manyinsert-update
user1001493
user1001493

Reputation: 87

INSERT into TABLE from another (one to many relation)

I have a table "Jobs" which got columns (JobID, Time, DrvNo...). I got another table "JobDetails" (JobFK, PkSequence, IsDestination, PC). There is one to many relation between Jobs and JobDetails.

Data in Jobs is as follows:
Jobs
==================
JobID, Time, DrvNO
==================
1, 12:30, 123
2, 13:50, 343
3, 14:00, 234

Data in JobDetails as follows:
JobDetails
==============================================
JobFK, PkSequence, IsDestination, PC
==============================================
1, 0, 0, AB1
1, 1, 1, DE1
2, 0, 0, RT1
2, 1, 0, DS1
2, 2, 1, KJ1
3, 0, 0, YU7
3, 1, 1, TH1

What I am trying to do is to merge these two tables by inserting two more columns in Jobs and trying to populate Jobs by getting the Pickup and Destination from JobDetails; like the following:

Jobs
==================
JobID, Time, DrvNO, Pickup, Dropoff
==================
1, 12:30, 123, AB1, DE1
2, 13:50, 343, RT1, KJ1
3, 14:00, 234, YU7, TH1

I've achieved the SELECT part of this, as follows:

SELECT cj.JobID, cj.Time,cj.DrvNo, j.PC AS Pickup, k.PC AS Dropoff
FROM Jobs AS cj
 LEFT JOIN JobDetails AS j ON (cj.JobID = j.JobFK AND j.PKSequence = 0)
 LEFT JOIN JobDetails AS k ON (cj.JobID = k.JobFK AND k.IsDestination= 1)

The above SELECT works fine but how to insert/update the existing table (Jobs) with data from this query?

Any help is appreciated. Thanks

Upvotes: 0

Views: 1095

Answers (1)

Anthony Hancock
Anthony Hancock

Reputation: 931

Here's the SQL to add the columns and then update them with the values from your select query (Didn't validate the joins since you say they are working for what you want). You can alter the types of the new columns as needed, just guessed based off of your given data. 

ALTER TABLE Jobs
    ADD Pickup VARCHAR(3)
        ,Dropoff VARCHAR(3)
;
GO

UPDATE cj
    SET cj.Pickup = j.PC
        ,cj.Dropoff = k.PC
FROM Jobs AS cj
 LEFT JOIN JobDetails AS j ON (cj.JobID = j.JobFK AND j.PKSequence = 0)
 LEFT JOIN JobDetails AS k ON (cj.JobID = k.JobFK AND k.IsDestination= 1)

Upvotes: 3

Related Questions