Tuesday, September 27, 2005

INSERT ... ON DUPLICATE KEY UPDATE

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/09/insert-on-duplicate-key-update/.

2 comments:

UltraVi01 said...

Nice Post! Could you kindly help me with a problem. I have this query: "SELECT *, COUNT(term) AS count_term FROM logs GROUP BY term HAVING count_term>1 AND term NOT LIKE 'Enter%' AND term NOT LIKE '' ORDER BY count_term DESC LIMIT 10"

It works great -- It displays the count for each term in the table. However, I added a field called "count" and I would also like to update all of the rows currently in the table with their count. Any help would be Greatly appreciated.
Best,
Ryan

Markus Popp said...

Hi,

If it's a one-time task, I think the easiest way is to materialize the query and temporarily create a table which you use to join to the logs table and update it with the count, like this:

CREATE TABLE logs_cnt
SELECT *, COUNT(term) AS count_term FROM logs GROUP BY term HAVING count_term>1 AND term NOT LIKE 'Enter%' AND term NOT LIKE '' ORDER BY count_term DESC;

ALTER TABLE logs_cnt ADD PRIMARY KEY(term);

UPDATE logs a INNER JOIN logs_cnt b USING (term)
SET a.count = b.count_term;

DROP TABLE logs_cnt;

I hope that helps ;).

Markus