user6305775 user6305775 - 4 months ago 11
SQL Question

Update and insert TSQL

How can i modify TSQL update statement below to insert

partid
of the source table is not in the partid field of Item ID ='500224' in destination table. if doesn't exist I need to insert.

USE [DBNAME];
GO
UPDATE m
SET [partId] = a.[partId]
select * FROM
[SourceTable] m
INNER JOIN
[DestTable] a
ON m.[bomItem] = a.[ItemId]
AND m.bomEntry = a.bomEntry AND m.bomRev = a.rev WHERE
m.bomRev=a.rev AND m.partId <> a.partid AND m.lineNbr = a.bomEntry ;
Go


In Source Table

enter image description here

In Destination Table

enter image description here

For Example in pictures above partid
100280
doesn't exist in Destination table i want to add that and keep all others the same.

Answer

You can insert the missing partid to the destination table, by using LEFT JOIN with NULL check.

INSERT INTO [DestTable] (bomItem, bomRev, bomEnty, lineNbr, dType, partId, revId, qty)
SELECT ST.ItemID, ST.rev, ST.bomEnty, ST.lineNbr, 0 AS dType, ST.partid, NULL AS revId, ST.qty
FROM [SourceTable] ST
LEFT JOIN [DestTable] DT ON DT.bomItem = ST.ItemID AND DT.partid = ST.partid
        AND DT.bomRev = ST.Rev AND DT.bomEntry = ST.bomEntry -- remove these conditions if not need
WHERE DT.partid IS NULL

Since few of the columns is mismatching between the two tables, I have added proper column names in the SELECT

Comments