Update a value from one table to another using a Proc

Question

I have two tables with identical structure. One is a Temp table and one is the main table. The table names are PurchaseOrders_TEMP and PurchaseOrders respectively. Each day I refresh the temp table with new data and run a PROC to update the main table with the changes and/or additions. I realized I had an issue if a PO and/or an Item on a PO was completely deleted. My proc is not updating rows that are not in the Temp table. Here is the table definition for PurchaseOrders. I need either a new PROC or an update to this PROC that will change the PBSTAT to 'X' of the PBPO/PBITEM when it does not exist in the TEMP table.

[dbo].[PurchaseOrders](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PBPO] [int] NOT NULL,
    [PBSEQ] [int] NOT NULL,
    [PBITEM] [varchar](28) NOT NULL,
    [PBDEL] [varchar](1) NULL,
    [PBVEND] [varchar](9) NULL,
    [PBTYPE] [varchar](1) NULL,
    [PBLOC] [varchar](4) NULL,
    [PBDSC1] [varchar](51) NULL,
    [PBDSC2] [varchar](45) NULL,
    [PBPDTE] [datetime] NULL,
    [PBDUE] [datetime] NULL,
    [XDPRVDT] [datetime] NULL,
    [XDCURDT] [datetime] NULL,
    [PBRDTE] [datetime] NULL,
    [PBOQTY] [int] NULL,
    [PBTQTY] [int] NULL,
    [PBRQTY] [int] NULL,
    [PBDQTY] [int] NULL,
    [PBLQTY] [int] NULL,
    [PBBQTY] [int] NULL,
    [PBCOST] [float] NULL,
    [EXTCOST] [float] NULL,
    [PBCCTD] [datetime] NULL,
    [PBCCTT] [int] NULL,
    [PBCCTU] [varchar](15) NULL,
    [PBLCGD] [datetime] NULL,
    [PBLCGT] [int] NULL,
    [PBUSER] [varchar](12) NULL,
    [PASTAT] [varchar](1) NULL,
    [PABUYR] [varchar](3) NULL,
    [PAPAD3] [varchar](45) NULL,
    [PAPPHN] [varchar](30) NULL,
    [PACONF] [varchar](39) NULL,
    [Comment] [varchar](max) NULL

Here is the current Proc that I run to update the data.

ALTER PROCEDURE [dbo].[UpdateExistingPurchaseOrders] 
AS
BEGIN
SET NOCOUNT ON;

UPDATE 
    p
SET
    p.PBDEL = pt.PBDEL,
    p.PBVEND = pt.PBVEND,
    p.PBTYPE = pt.PBTYPE,
    p.PBLOC = pt.PBLOC,
    p.PBDSC1 = pt.PBDSC1,
    p.PBDSC2 = pt.PBDSC2,
    p.PBPDTE = pt.PBPDTE,
    p.PBDUE = pt.PBDUE,
    p.PBRDTE = pt.PBRDTE,
    p.PBOQTY = pt.PBOQTY,
    p.PBTQTY = pt.PBTQTY,
    p.PBRQTY = pt.PBRQTY,
    p.PBDQTY = pt.PBDQTY,
    p.PBLQTY = pt.PBLQTY,
    p.PBBQTY = pt.PBBQTY,
    p.PBCOST = pt.PBCOST,
    p.EXTCOST = pt.EXTCOST,
    p.PBCCTD = pt.PBCCTD,
    p.PBCCTT = pt.PBCCTT,
    p.PBCCTU = pt.PBCCTU,
    p.PBLCGD = pt.PBLCGD,
    p.PBLCGT = pt.PBLCGT,
    p.PBUSER = pt.PBUSER,
    p.PASTAT = pt.PASTAT,
    p.PABUYR = pt.PABUYR,
    p.PAPAD3 = pt.PAPAD3,
    p.PAPPHN = pt.PAPPHN,
    p.PACONF = pt.PACONF
FROM
    dbo.PurchaseOrders_TEMP pt
    LEFT OUTER JOIN dbo.PurchaseOrders p
        ON p.PBPO = pt.PBPO
        AND p.PBSEQ = pt.PBSEQ
WHERE
    p.PBPO IS NOT NULL
    AND p.PBSEQ IS NOT NULL

END

score 1 · Answer 1 · answered Aug 22 '13 at 14:37

1

You can use the MERGE statement to modify data in a target table from data in a source query. The first two examples in the docs show how to insert/update or update/delete depending on whether a source row is found.

In your case you would have to write something like this:

MERGE dbo.PurchaseOrders AS target
USING (SELECT ...  FROM PurchaseOrders_TEMP) AS source (...)
ON (ON target.PBPO = source.PBPO
    AND target.PBSEQ = source.PBSEQ)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
WHEN NOT MATCHED BY TARGET
    THEN INSERT (....)
         VALUES (....)
WHEN MATCHED 
    THEN UPDATE 
    SET ...

answered Aug 22 '13 at 14:37

Panagiotis Kanavos

120,703
13
188
236

I don't want to delete any of the history in the PurchaseOrders table. I just want to change the value of the field PASTAT to 'X' when the PBPO and or PBSEQ/PBITEM does not exist in the new data. – tsqln00b Aug 22 '13 at 14:42
Then write an UPDATE statement instead of DELETE – Panagiotis Kanavos Aug 22 '13 at 14:42
Anyone else have a way to do this? – tsqln00b Aug 22 '13 at 17:07
Did you try it? Did you have a problem? What other way are you looking? What didn't work? – Panagiotis Kanavos Aug 22 '13 at 18:23

score 0 · Answer 2 · answered Aug 23 '13 at 13:16

Here is the answer I came up with...

UPDATE 
   p
SET
   p.PASTAT = 'X'
FROM
   dbo.PurchaseOrders_TEMP pt        
   LEFT JOIN dbo.PurchaseOrders p ON 
       p.PBPO = pt.PBPO
       AND p.PBSEQ = pt.PBSEQ
WHERE
   pt.PBPO IS NULL
   AND pt.PBSEQ IS NULL
   AND p.PASTAT <> 'X'
END

Update a value from one table to another using a Proc

2 Answers2