Update ved duplering...

Ja det er der da. Der findes en dejlig MERGE funktion i SQL Server 2008, der kan indsætte nye rows, og updatere eksisterende i ét hug.

Har du mulighed for at give mig et eksempel på hvordan jeg skal lave denne merge?

Har prøvet flg. men fået fejl.


merge into tabel using tabel on
(where kamp = 5)
when matched then update set status=7

Dette er fejlen:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'into'.

Et eksemple jeg har hugget et tilfældigt sted på nettet:

Here's a more detailed example to demonstrate how to use the MERGE statement. This script updates the Stock table based on daily trades tracked in the Trades table.



CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK(Qty > 0));

CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY,  Delta INT);

GO



INSERT Stock VALUES ('MSFT', 10);
INSERT Stock VALUES ('TXN', 5);

INSERT Trades VALUES ('MSFT', 5);
INSERT Trades VALUES ('TXN', -5);
INSERT Trades VALUES ('SBUX ', 3);
GO



-- Apply changes to the Stock table based on daily trades
-- tracked in the Trades table. Delete a row from the Stock table
-- if all the stock has been sold. Update the quantity in the Stock
-- table if you still hold some stock after the daily trades. Insert
-- a new row if you acquired a new Stock.
-- As a result, TXN is deleted, SBUX inserted, MSFT updated

MERGE Stock S -- target table
        USING Trades T -- source table
        ON S.Stock = T.Stock
        WHEN MATCHED AND (Qty + Delta = 0) THEN
                DELETE -- delete stock if entirely sold
        WHEN MATCHED THEN
                -- update stock if you still hold some stock
                UPDATE SET Qty = Qty + Delta
        WHEN NOT MATCHED THEN
                -- insert a row if the stock is newly acquired
                INSERT VALUES (Stock, Delta)
        -- output details of INSERT/UPDATE/DELETE operations
        -- made on the target table
        OUTPUT $action, inserted.Stock, deleted.Stock;


SELECT * FROM Stock;
GO