CODEWITHSUNDEEP
sqlms-access
Peter Burke
Peter Burke

Reputation: 45

UPDATE Statement SQL

In the picture below I have a table that I performed an INSERT query on. This SQL Query adds the information as new records. I want to add them to the records that already exist (as seen in the picture). Would that be possible? Maybe with an UPDATE Statement? Any help would be greatly appreciated.

table data view

Current SQL Statement:

INSERT INTO ShouldImportMetricsIDsTable
            (FormularyName,ClientName)
SELECT [Formulary Name],
       [Client Name]
FROM   FN_DataDump_ALL_02012015 as F
WHERE  EXISTS (SELECT 1
               FROM   mytable as M
               WHERE  F.formularyId = M.formularyId)

Upvotes: 0

Views: 84

Answers (1)

Phrancis
Phrancis

Reputation: 2282

Something like this should do the trick:

UPDATE ShouldImportMetricsIDsTable AS Imp
INNER JOIN FN_DataDump_ALL-02012015 AS F
    ON Imp.FormularyId = F.FormularyId
SET Imp.FormularyName = F.FormularyName,
    Imp.ClientName = F.ClientName
WHERE Imp.FormularyId = F.FormularyId

I didn't feel that joining myTable would be needed, but if you need to just add this before SET:

INNER JOIN myTable AS M
    ON F.formularyId = M.formularyId

If you do that you'll probably need to use some of those wonky parentheses from Access SQL, like this:

UPDATE ((ShouldImportMetricsIDsTable AS Imp
INNER JOIN FN_DataDump_ALL-02012015 AS F
    ON Imp.FormularyId = F.FormularyId)
INNER JOIN myTable AS M
    ON F.formularyId = M.formularyId)
SET Imp.FormularyName = F.FormularyName,
    Imp.ClientName = F.ClientName
WHERE Imp.FormularyId = F.FormularyId

Upvotes: 2

Related Questions