CODEWITHSUNDEEP
sqlsql-servert-sqlsql-updatesql-insert
Dawson
Dawson

Reputation: 573

Moving data from one table to another while also creating entries when no corresponding row exists

I have two tables CourtCase and CaseResult. CaseResult contains the foreign key CourtCaseID which refers to the CourtCase table.

The table CourtCase contains a field CompleteDate. I would like to migrate CompleteDate from the CourtCase table to the CaseResult table.

The query I have come up with to migrate the data:

UPDATE CaseResult 
SET CaseResult.CompleteDate = CourtCase.CompleteDate 
FROM CourtCase, CaseResult 
WHERE CourtCase.CourtCaseID = CaseResult.CourtCaseID

The issue is that not every CourtCase has an entry in CaseResult, only some do. So i would need to move over the dates for cases that exist with a query like the above, but also create the entries in CaseResult for the cases that don't exist, and insert the foreign key CourtCaseID and CompleteDate.

Upvotes: 0

Views: 67

Answers (3)

Arrmaniac
Arrmaniac

Reputation: 81

You want to use MERGE in order to accomplish your goal: That allows you to define what you want to do for each row found in CourtCase that you want to be represented in CaseResult. A rough example:

MERGE CaseResult AS r
USING CourtCase AS c
ON (r.CourtCaseID = c.CourtCaseID)
WHEN NOT MATCHED BY CaseResult
   THEN INSERT (CourtCaseID, CompleteDate) VALUES (c.CourtCaseID, c.CompleteDate)
WHEN MATCHED
   THEN UPDATE SET r.CompleteDate = c.CompleteDate
;

See MERGE under https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/bb522522(v=sql.100)?redirectedfrom=MSDN or https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/bb510625%28v%3dsql.100%29 for detailed information.

(removed earlier MySQL answer)

Upvotes: 0

Marc Guillot
Marc Guillot

Reputation: 6455

You can do it in two steps, first you update the existing records, and second you create the new records. I also encourage you to use proper JOINs.

UPDATE CaseResult 
SET CaseResult.CompleteDate = CourtCase.CompleteDate 
FROM CourtCase
     INNER JOIN CaseResult ON CourtCase.CourtCaseID = CaseResult.CourtCaseID

INSERT INTO CaseResult (CourtCaseID, CompleteDate)
       SELECT CourtCaseID, CompleteDate 
       FROM CourtCase
       WHERE NOT EXISTS (SELECT * FROM CaseResult WHERE CaseResult.CourtCaseID = CourtCase.CourtCaseID)

Upvotes: 1

GMB
GMB

Reputation: 222402

In SQL Server, you can do this in a single statement, using the MERGE syntax, which implements the "update or insert" logic, also called upsert:

merge CaseResults cr
    using CourtCase cc
    on (cr.CourtCaseID = cc.CourtCaseID)
when matched then update set 
    cr.CompleteDate = cc.CompleteDate
when not matched by target then insert (CourtCaseID, CompleteDate)
    values(cc.CourtCaseID, cc.CompleteDate)

You did not tell all columns that are available in the table. This assumes just two colums, CourtCaseID and CompleteDate. You might need to adjust the insert part with more columns, according to your actual schema.

Upvotes: 2

Related Questions