Most Efficient MySQL query to update a table from identical memory table?

You could use a INSERT ... ON DUPLICATE KEY UPDATE query - but that depends on the primary keys or UNIQUE indexes on your master table INSERT INTO > (Item1, Item2, cnt) SELECT Item1, Item2, cnt FROM > ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt).

You could use a INSERT ... ON DUPLICATE KEY UPDATE query - but that depends on the primary keys or UNIQUE indexes on your master table. INSERT INTO > (Item1, Item2, cnt) SELECT Item1, Item2, cnt FROM > ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt).

Trying to edit my answer but it seems that because I deleted the anwser and undeleted it afterwards, SO prevents any further editing ("not found" error; perhaps a caching issue). The ON DUPLICATE KEY UPDATE should read ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt) if cnt is not always 1 in the memory table. – Stefan Gehrig Jun 24 '09 at 11:53 Thank you!

It works. However something seems strange to me. The solution that worked for me is "INSERT INTO linked_items SELECT * FROM linked_items_mem as li_mem ON DUPLICATE KEY UPDATE linked_items.

Cnt=linked_items. Cnt+li_mem. Cnt;" However in my tests the memory table has 26 rows but mysql says 48 rows were affected.

How come? – Nir Jun 24 '09 at 12:01 1 @Nir: Each INSERT and UPDATE operation counts separately. In your case there were 26 INSERT's, of which 22 failed and lead to 22 UPDATE's, thus making 26 + 22 = 48 operations.

– Quassnoi Jun 24 '09 at 12:03 Also note that MySQL doesn't count UPDATE's which update nothing. UPDATE table SET cnt = cnt will always return 'no rows affected'. If your cnt is a 0 in one or your rows, then the row value will remain the same and this row won't contribute into the affected rows count.

– Quassnoi Jun 24 '09 at 13:14.

Create a PRIMARY KEY on the disk table: ALTER TABLE maintable ADD CONSTRAINT pk_maintable_item1_item2 (item1, item2) and issue the following query: INSERT INTO maintable SELECT item1, item2, COUNT(*) AS cnt FROM memtable mem GROUP BY item1, item2 ON DUPLICATE KEY UPDATE cnt = maintable. Cnt + mem. Cnt Note, however, that if you have lots of DISTINCT item1, item2 pairs, then the solution proposed by @S.

Gehrig will most probably work better (due to overhead on GROUP BY).

I like that idea of grouping the rows prior to insertion. +1 – Stefan Gehrig Jun 24 '09 at 11:28 @S. Gehrig: It won't work without this.

MySQL can update each row in a table at most once within a single update query, see this: bugs.mysql. Com/bug. Php?

Id=44494 – Quassnoi Jun 24 '09 at 11:31 @Quassnoi: Thanks for that info. Deleted my answer as it won't work accoding to the bug report above. – Stefan Gehrig Jun 24 '09 at 11:35 @S.

Gehrig: ummm... sorry, just checked, it's only applicable to an UPDATE query. INSERT ON DUPLICATE KEY UPDATE works fine. Restore your answer, I'll upvote it, since it many cases it will be more efficient than grouping :) – Quassnoi Jun 24 '09 at 11:38.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.