Update SQL Server table based on values in a column

Question

I have 11 tables in my SQL Server database in which have the same columns, 10 are sub tables and 1 is the main table where the data in 10 tables will be merged. The 10 tables are constantly updating from time to time, below is the column that my 11 tables has:

[DATE], [SOURCE], [DESTINATION], [REFERENCE#], [ITEMCODE], [DESCRIPTION],
[UM], [PRICE], [QTY], [AMOUNT], [MFGDATE], [EXPDATE], [LOT#], [TRANS], [CONSIGNOR], [DRDATE]

When a user updates a table, i.e Table1, the main table should automatically bee updated based on the SOURCE and DESTINATION columns. I know that there is the Merge statement, where you can update, insert and delete all at the same time, but my problem with that is the delete, since Table1 will only have partial data compared to the main table, it will delete the rest of the data in main table that is not present in the Table1.

I am new to SQL Server and I can't pull this off by myself, that's why I am here looking for ideas and some help too.

You can easily set up MERGE to only update existing rows - and NOT delete anything! — marc_s
– marc_s, Commented Jun 24, 2019 at 6:20
Why this design? I mean, why do you need the 11th table? Why not use a view? — Zohar Peled
– Zohar Peled, Commented Jun 24, 2019 at 6:24
Is it possible? sorry I don't know, that's why I am asking, something that it will look up source and destination in the main table and compare it in Table1 and update, insert and delete? — Antonio
– Antonio, Commented Jun 24, 2019 at 6:25
You can use a Trigger for that, but I won't use MERGE as @marc_s because it has some issues, instead I would use EXISTS() — Ilyes
– Ilyes, Commented Jun 24, 2019 at 6:25
Do all these tables have the same columns? Can you provide sample data as DDL and DML (create table and insert statements) and expected results? — Zohar Peled
– Zohar Peled, Commented Jun 24, 2019 at 6:32

Tim Biegeleisen · Accepted Answer · 2019-06-24 06:23:11Z

1

You may try using an after insert trigger on the Table1 table:

CREATE TRIGGER your_trigger
ON Table1
AFTER INSERT AS
BEGIN
    UPDATE a
    SET SOURCE = b.SOURCE, DESTINATION = b.DESTINATION
    FROM Main a
    INNER JOIN INSERTED b
        ON a.ID = b.ID
END
GO

I assume that Table1 and the main table are connected via a column called Id. However, you may update the join logic to use whichever column(s) should determine how to connect the two tables.

answered Jun 24, 2019 at 6:23

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Antonio · Accepted Answer · 2019-06-26 02:50:40Z

after reading your comments and suggestion and also reading a lot more in other site, I have come up with this:

MERGE DelTrans2 as trgt
USING testTable1 src
    ON trgt.SOURCE = Src.SOURCE
    AND trgt.DESTINATION = Src.DESTINATION
    AND trgt.REFERENCE# = Src.REFERENCE#
    AND trgt.ITEMCODE = Src.ITEMCODE
    AND trgt.ID = Src.ID
WHEN NOT MATCHED BY TARGET
    AND Src.ID = 1001 THEN
    INSERT(DATE, SOURCE, DESTINATION, REFERENCE#, ITEMCODE, DESCRIPTION, UM, PRICE, QTY, AMOUNT, MFGDATE, EXPDATE, LOT#, TRANS, CONSIGNOR, DRDATE, ID)
    VALUES(DATE, SOURCE, DESTINATION, REFERENCE#, ITEMCODE, DESCRIPTION, UM, PRICE, QTY, AMOUNT, MFGDATE, EXPDATE, LOT#, TRANS, CONSIGNOR, DRDATE, ID)
WHEN MATCHED AND 
    (
        ISNULL(trgt.DATE, '') <> ISNULL(Src.DATE, '')
        OR
        ISNULL(trgt.SOURCE, '') <> ISNULL(Src.SOURCE, '')
        OR
        ISNULL(trgt.DESTINATION, '') <> ISNULL(Src.DESTINATION, '')
        OR
        ISNULL(trgt.REFERENCE#, '') <> ISNULL(Src.REFERENCE#, '')
        OR
        ISNULL(trgt.ITEMCODE, '') <> ISNULL(Src.ITEMCODE, '')
        OR
        ISNULL(trgt.DESCRIPTION, '') <> ISNULL(Src.DESCRIPTION, '')
        OR
        ISNULL(trgt.UM, '') <> ISNULL(Src.UM, '')
        OR
        ISNULL(trgt.PRICE, '') <> ISNULL(Src.PRICE, '')
        OR
        ISNULL(trgt.QTY, '') <> ISNULL(Src.QTY, '')
        OR
        ISNULL(trgt.AMOUNT, '') <> ISNULL(Src.AMOUNT, '')
        OR
        ISNULL(trgt.MFGDATE, '') <> ISNULL(Src.MFGDATE, '')
        OR
        ISNULL(trgt.EXPDATE, '') <> ISNULL(Src.EXPDATE, '')
        OR
        ISNULL(trgt.LOT#, '') <> ISNULL(Src.LOT#, '')
        OR
        ISNULL(trgt.TRANS, '') <> ISNULL(Src.TRANS, '')
        OR
        ISNULL(trgt.CONSIGNOR, '') <> ISNULL(Src.CONSIGNOR, '')
        OR
        ISNULL(trgt.DRDATE, '') <> ISNULL(Src.DRDATE, '')
        OR
        ISNULL(trgt.ID, '') <> ISNULL(Src.ID, '')
    )
    AND Src.ID = 1001
THEN
    UPDATE SET DATE = Src.DATE, SOURCE = Src.SOURCE, DESTINATION = Src.DESTINATION, REFERENCE# = Src.REFERENCE#, ITEMCODE = Src.ITEMCODE, DESCRIPTION = Src.DESCRIPTION, UM = Src.UM, PRICE = Src.PRICE, QTY = Src.QTY, AMOUNT = Src.AMOUNT, MFGDATE = Src.MFGDATE, EXPDATE = Src.EXPDATE, LOT# = Src.LOT#, TRANS = Src.TRANS, CONSIGNOR = Src.CONSIGNOR, DRDATE = Src.DRDATE, ID = Src.ID
WHEN NOT MATCHED BY SOURCE
    AND trgt.ID = 1001 THEN
    DELETE;

I know this is not the most efficient way, but this works fine with what I want.

Thank you for your help.

Collectives™ on Stack Overflow

Update SQL Server table based on values in a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related