INSERT ON DUPLICATE KEY: Add values together

Question

I need to change the date column of an SQL table that contains dates and amounts from, e.g. 20170101 to 20170102. However it can be the case that the new date 20170102 already exists. This results in an duplicate key error, because the date column is part of a unique index.

My first thought was to use INSERT ON DUPLICATE KEY:

INSERT INTO table (Date, Amount)
SELECT '20170102', Amount
FROM table
WHERE Date = '20170101'
ON DUPLICATE KEY UPDATE Amount = OldAmount + NewAmount

The part Amount = OldAmount + NewAmount cannot work obviously. But how can I solve my issue?

The `mysql` and `tsql` tags are, as a rule, incompatible. Please tag your question with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Dec 14 '17 at 17:24
Does [this](https://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update) question help? — HABO, Dec 14 '17 at 17:34

score 0 · Answer 1 · answered Dec 14 '17 at 18:55

Here is your answer: Assume old table TABLE1 ( DATE1 DATE PRIMARY KEY, AMOUNT INT);

Add new column Date2 as DATE:

ALTER TABLE TABLE1 ADD COLUMN DATE2 DATE;
Generate all DATE2 by old date;

UPDATE TABLE1 set DATE2=DATE1; -- OLD column DATE1 or DATE
UPDATE DATE2 which record you want to update DATE

UPDATE TABLE1 SET DATE2='20170101' where DATE1='20170102'; -- Assume 20170101 is already exists
Get the records with added amount

SELECT DATE2,sum(AMOUNT) from TABLE1 group by 1; -- This will show all records with sum
You can create new table TABLE2 and insert those records:

CREATE TABLE TABLE2 ( DATE1 DATE PRIMARY KEY, AMOUNT int); INSERT INTO TABLE2 SELECT DATE2, SUM(AMOUNT) FROM TABEL1 GROUP BY 1;

score 0 · Answer 2 · answered Dec 18 '17 at 08:18

If you're using SQL server, try a merge statement.

Just to clarify - Do you need to change the dates of records in the table or just add new records?

CREATE TABLE #DataTable
(
    SomeDate DATE,
    Amount INT
)

INSERT #DataTable
VALUES
('20170101', 1),
('20170206', 2),
('20170309', 3),
('20170422', 4),
('20170518', 5)

DECLARE @NewValues TABLE
(
    SomeDate DATE,
    Amount INT
)

INSERT @NewValues
VALUES
('20170101', 10), --Update
('20170309', 15), --Update
('20170612', 6), --Insert
('20170725', 7) --Insert

MERGE INTO #DataTable AS tgt
USING @NewValues AS nv
    ON  nv.SomeDate = tgt.SomeDate
WHEN NOT MATCHED THEN INSERT
VALUES
(nv.SomeDate, nv.Amount)
WHEN MATCHED THEN UPDATE
SET tgt.Amount = tgt.Amount + nv.Amount
OUTPUT  $action AS MergeAction, 
        Inserted.SomeDate, 
        Deleted.Amount AS OldValue, 
        Inserted.Amount AS NewValue;

DROP TABLE #DataTable;

INSERT ON DUPLICATE KEY: Add values together

2 Answers2