0

I have a problem with a MySQL Update statement. I have more than 100.000 Entries in the table. So my approaches were not successful. (see below)

First, I want to count the number of values (thats easy):

SELECT values1 ,count(values1) FROM table
GROUP BY value1
HAVING COUNT(value1) <= 1000;

Second, i want to replace some values of the column values1 which appears only <= 1000 times.

So I tried these statement:

Update table as t
SET t.value1 = "limitAmount" 
WHERE EXISTS (select value1 from 
(select * from table) as f Group by f.value1
Having count(f.value1) <= 1000);

When i tried this SQL statement, i received:

Error Code 1205. Lock Wait timeout exceeded. Try restarting transaction.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
rXhalogene
  • 13
  • 4

2 Answers2

1

Try this

Update 
  table as t 
SET 
  t.value1 = "limitAmount" 
WHERE 
  EXISTS (select value1 from (select * from table) as f where f.value1 = t.value1 Group by f.value1 Having count(f.value1) <= 1000);

Notice where f.value1 = t.value1 condition in the subquery

Akash
  • 4,956
  • 11
  • 42
  • 70
  • i get this from the MySQL Workbench 0 21:34:39 Error Code: 1205. Lock wait timeout exceeded; try restarting transaction – rXhalogene May 04 '13 at 19:37
  • it seems that some other transaction has locked one of the rows you are currently updating, you can do a re-start which would free all the locks and also have a look at `SHOW ENGINE INNODB STATUS` before doing so – Akash May 04 '13 at 19:41
  • I did a "SHOW ENGINE INNODB STATUS". What is the point of interest? – rXhalogene May 04 '13 at 19:58
  • the above statement would inform you of the locks present on the table – Akash May 04 '13 at 20:01
  • you mean this part: 3671 lock struct(s), heap size 358840, 390120 row lock(s), undo log entries 3104 – rXhalogene May 04 '13 at 20:11
  • Yes, they mention that the tables are alredy locked and when we fire the update query, it is blocked unless the previous locks are free. You would either need to kill the query that previously running (the one that's holding the lock) or restart your DB (which would free all the locks instead) – Akash May 04 '13 at 20:13
  • Okay i'restarted my DB. And do you have any advice for configuration? because i tried the update and for 6minutes i did not get a "lock error", but the db was still working. My PC has a quadcore and 12gb of ram. I thought it would not take so much time for one query. – rXhalogene May 04 '13 at 20:19
  • you may try increasing the `lock wait timeout`, but would be better to chk what's causing the lock, here's more info in it: http://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im – Akash May 05 '13 at 04:51
0

This statement works:

UPDATE test SET val="BLUB" WHERE val IN (SELECT val FROM (SELECT val FROM test GROUP BY val HAVING COUNT(val) <2000) war);

alternative:

  1. Adding Values to a separate table INSERT INTO helpTable (Value1) (select val FROM test Group by val Having count(val) <= 2000);

  2. Select Update test as t SET t.val = "BLUB" WHERE NOT EXISTS (Select Value1 FROM helpTable as h WHERE t.val = h.Value1);

rXhalogene
  • 13
  • 4