How do I UPDATE a field from another table

Question

I have two tables that have the same data and fields except for one. I want to UPDATE the table that is missing the field 'SBCMP'. Here is the definition of the table I want to add the field and data to:

[dbo].[SalesData](
    [SBLOC] [varchar](3) NULL,
    [SBCUST] [varchar](7) NULL,
    [RMNAME] [varchar](30) NULL,
    [IFPRVN] [varchar](6) NULL,
    [SBITEM] [varchar](25) NULL,
    [SBITD1] [varchar](50) NULL,
    [SBDIV] [smallint] NULL,
    [SBCLS] [smallint] NULL,
    [SBQSHP] [smallint] NULL,
    [AVC] [real] NULL,
    [SBEPRC] [real] NULL,
    [SBINV] [int] NULL,
    [SBORD] [int] NULL,
    [SBTYPE] [varchar](1) NULL,
    [SBINDT] [datetime] NULL,
[RMSTAT] [varchar](2) NULL

The other table has the exact same table definitions except it has the field [SBCMP] [smallint] NULL

I would just use the new table, but the old table has other older data that the new one does not.

I just want to know what is the best method of UPDATE for the table to add the field and data.

Giannis Paraskevopoulos · Accepted Answer · 2013-08-07T20:13:49.057

First of all design the table and add the new field.

Or run

ALTER TABLE SalesData
ADD SBCMP smallint NULL

Then you may use MERGE in order to get data.

MERGE SalesData AS target
USING (SELECT * FROM SalesDataNew) AS source 
      ON (target.IDField = source.IDField )
WHEN MATCHED THEN 
    UPDATE SET SBLOC = source.SBLOC,
               SBCUST = source.SBCUST ,
               RMNAME = source.RMNAME,
               IFPRVN = source.IFPRVN ,
               SBITEM = source.SBITEM ,
               SBITD1 = source.SBITD1 ,
               SBDIV = source.SBDIV ,
               SBCLS = source.SBCLS ,
               SBQSHP = source.SBQSHP ,
               AVC = source.AVC,
               SBEPRC = source.SBEPRC,
               SBINV = source.SBINV ,
               SBORD = source.SBORD,
               SBTYPE = source.SBTYPE,
               SBINDT = source.SBINDT,
               RMSTAT = source.RMSTAT ,
               SBCMP = source.SBCMP
WHEN NOT MATCHED THEN   
    INSERT (SBLOC,
               SBCUST ,
               RMNAME,
               IFPRVN ,
               SBITEM ,
               SBITD1 ,
               SBDIV  ,
               SBCLS  ,
               SBQSHP ,
               AVC ,
               SBEPRC ,
               SBINV ,
               SBORD,
               SBTYPE,
               SBINDT,
               RMSTAT,
               SBCMP )
    VALUES (source.SBLOC,
               source.SBCUST ,
               source.RMNAME,
               source.IFPRVN ,
               source.SBITEM ,
               source.SBITD1 ,
               source.SBDIV  ,
               source.SBCLS  ,
               source.SBQSHP ,
               source.AVC ,
               source.SBEPRC ,
               source.SBINV ,
               source.SBORD,
               source.SBTYPE,
               source.SBINDT,
               source.RMSTAT
               source.SBCMP)

Keep in mind that i have used an imaginary field called IDField in the ON clause of the MERGE. This is imaginary as it is not clear which is the id of the table. If there are two columns forming the id you should add them as you would in a JOIN statement.

One more thing is that i have named the new table as SalesDataNew as i didn't know its actual name.

The MERGE is FULL OUTER JOINning the two tables (called target and source). Then for matched rows it is performing an UPDATE and for rows not matched that are on the source and not on the target it performs an INSERT. Both UPDATE and INSERT are performed on the target.

It could be possible to do something on the target when there are rows on the target but not on the source (here you usually delete) but this is out of scope i believe.

If you just want to UPDATE and not INSERT then the above is ok for you (though you should remove the WHEN NOT MATCHED THEN part. You can also do a straight UPDATE.

An example would be:

UPDATE     SalesData
SET        SBLOC = source.SBLOC,
           SBCUST = source.SBCUST ,
           RMNAME = source.RMNAME,
           IFPRVN = source.IFPRVN ,
           SBITEM = source.SBITEM ,
           SBITD1 = source.SBITD1 ,
           SBDIV = source.SBDIV ,
           SBCLS = source.SBCLS ,
           SBQSHP = source.SBQSHP ,
           AVC = source.AVC,
           SBEPRC = source.SBEPRC,
           SBINV = source.SBINV ,
           SBORD = source.SBORD,
           SBTYPE = source.SBTYPE,
           SBINDT = source.SBINDT,
           RMSTAT = source.RMSTAT ,
           SBCMP = source.SBCMP
FROM       SalesData target
           JOIN SalesDataNew source
               ON target.IDField = source.IDField

The new table is designed in the same manner as the old table with the only exception being the addition of [SBCMP]. — tsqln00b, Aug 07 '13 at 19:15
I just want to UPDATE SBCMP when they match. Is that okay to just do that? — tsqln00b, Aug 07 '13 at 19:26
@tsqln00b You may check my updated solution. I have provided both MERGE and UPDATE solutions. MERGE in this case won't be much different than the UPDATE, and it is superior in cases that you want to either UPDATE, INSERT or DELETE, as it scans the tables only once. — Giannis Paraskevopoulos, Aug 07 '13 at 19:34
@tsqln00b If you tell me which are the columns that i should do the JOIN with i can edit the solution accordingly. — Giannis Paraskevopoulos, Aug 07 '13 at 19:35
I don't currently have an ID field on SalesDataNew. All the fields between the two tables are the same with SBCMP being the exception. I would think that SBLOC, SBCUST, SBITEM and SBORD would be unique to JOIN on. I think you have the tables reversed in the UPDATE solution. — tsqln00b, Aug 07 '13 at 19:38
In both solutions i am updating SalesDataNew. In the JOIN it doesn't matter what the order is. Since you only want to update one field and the rest should be the same, you could JOIN on all fields. — Giannis Paraskevopoulos, Aug 07 '13 at 20:04
UPDATE SalesDataNew is how is starts, however I want SalesData to be updated with SBCMP data. — tsqln00b, Aug 07 '13 at 20:12

How do I UPDATE a field from another table

1 Answers1