What is the most efficient way to UPDATE existing rows and INSERT non-exist rows in a table-copy operation?

Question

I have 2 tables:

stock:

pid name qty
--- ---- ---
1   aaaa   2
2   bbbb   3
1   aaaa   5
3   cccc   1
2   bbbb   2

stock_total:

pid name total_qty
--- ---- ---------

I can insert rows from stock table with the total qty to stock_total using this query

INSERT INTO stock_total (pid, name, total_qty)
SELECT pid, name, SUM(qty) 
FROM stock
GROUP BY pid, name

The problem is, I will run the SQL above via cron job. So on the next execution, the SQL should UPDATE existing product and INSERT non-exist products.

It would be very inefficient if I loop over the SELECT results, check each row if exists in stock_total and do the INSERT or UPDATE.

Is there any simpler way for achieving this? perhaps by modifying the SQL above. Thanks.

possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — juergen d, May 24 '12 at 08:21
Which brand of SQL? MySQL, SQL Server, Oracle, etc? Different implementations have different options available to them. — MatBailie, May 24 '12 at 08:21

score 1 · Answer 1 · edited May 24 '12 at 08:51

1

Use DUPLICATE KEY UPDATE:

INSERT INTO TABLENAME(col1, col2)
VALUES (@value, ‘yyy’)
ON DUPLICATE KEY UPDATE col1 = @value

edited May 24 '12 at 08:51

Michał Powaga

22,561
8
51
62

answered May 24 '12 at 08:22

Ganesh Bora

1,133
9
17

Vince Pergolizzi · Answer 2 · 2012-05-24T14:27:08.227

1

This for the update:

UPDATE stock_total
SET total_qty = SUM(s.qty)
FROM stock_total st
INNER JOIN stock s
ON st.pid = s.pid
AND st.name = s.name
WHERE s.pid = st.pid
GROUP BY s.pid

And this for the insert:

INSERT INTO stock_total
SELECT s.pid, s.name, SUM(s.qty)
FROM stock s
WHERE s.pid NOT IN (SELECT pid FROM stock_total)
GROUP BY s.pid, s.name

Should be fine, give it a try.

edited May 24 '12 at 14:27

answered May 24 '12 at 08:23

Vince Pergolizzi

6,396
3
19
16

Thanks Majid, Im not familiar with this syntax I work with SQL Server, feel free to edit my post with your suggestions if it will improve the query or the answer. – Vince Pergolizzi May 24 '12 at 09:25
The `UPDATE` doesn't work and the `INSERT` missed the `GROUP BY`. But I get your point. Thanks. – flowfree May 24 '12 at 11:45
Thanks, I fixed the insert. What error do you get on the update? – Vince Pergolizzi May 24 '12 at 14:27

score 0 · Answer 3 · answered May 24 '12 at 08:24

0

Why dont you call a Stored Procedure from the job?

In the SP in the block catch the DUPLICATE KEY exception and UPDATE. If no EXCEPTION is thrown it will INSERT.

answered May 24 '12 at 08:24

JHS

7,761
2
29
53

I'm a no-SP fans, but nice answer. – flowfree May 24 '12 at 08:42

Addicted · Answer 4 · 2012-05-24T08:35:55.523

merge is what you are searching for. Use it as follows -

merge into stock_total s " +
                        "using (select ? pid, ? name, ? total_qty from dual) d " +
                        "on (s.pid = d.pid and s.name = d.name and s.total_qty = d.total_qty) " +
                        "when matched then " +
                        "update set s.pid= d.pid, s.name = d.name, s.total_qty = d.total_qty" +
                        "when not matched then " +
                        "insert (pid, name, total_qty) " + 
                        "values(d.pid, d.name, d.total_qty)" ;

EDIT(for mySQL):

MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

I answered it according to OP's original question, he then added it to mysql tag. but this works for oracle very well. for mysql see my edit... — Addicted, May 24 '12 at 08:33
I think the end SQL would be very long and the `WITH (HOLDLOCK)` freaked me out :). But thanks. — flowfree, May 24 '12 at 11:55

score 0 · Accepted Answer · answered May 24 '12 at 12:16

After some googling and experimenting with the answers here, I came up with this solution. It turns out that MySQL supports REPLACE INTO... and ...ON DUPLICATE KEY UPDATE. So my query would be like this:

REPLACE INTO stock_total
SELECT pid, name, SUM(qty)
FROM stock
GROUP by pid, name

or,

INSERT INTO stock_total
SELECT pid, name, SUM(qty)
FROM stock
GROUP by pid, name
ON DUPLICATE KEY UPDATE total_qty=VALUES(total_qty)

If a row exists on stock_total, the first query will DELETE then INSERT the new row, and the second query will UPDATE the existing row.

Both query will only work if the table has a primary key or unique index:

CREATE TABLE stock_total (
    pid         INT         NOT NULL,
    name        VARCHAR(20) NOT NULL,
    total_qty   INT         NOT NULL,

    UNIQUE (pid, name)
);

Documentation:

What is the most efficient way to UPDATE existing rows and INSERT non-exist rows in a table-copy operation?

5 Answers5