1

In a MySQL-Table I try to update it as follows:

UPDATE MyTable SET active=2 WHERE ((active=1) AND (If the number of rows > 20 then active=2) );

I mean, the WHERE-Condition should be (active=1) and the second condition the number of rows in table should be limited to 20. Below is my wrong approach. Maybe it helps to understand what I'm trying to say or do:

UPDATE MyTable SET active=2 WHERE ( (active=1) AND
((SELECT id, @rownum:=@rownum + 1 as Row_Number from MyTable) JOIN (SELECT @rownum := 0) r)
, Row_Number>20)

In order to understand it better, I try to write the SQL-Statement like a code-snippet as follows:

if(Row_Number<20) {
// It's not interesting.
}else {
    for(var i=20; i<NumberOfRecords; i++) {
        active=2;
    }

}

Any idea how can I write a correct MySQL-Statement in order to accomplish my intend. Thanks in advance.

user3815508
  • 369
  • 4
  • 20
  • From your question, it appears that it would be extremely useful for you to maintain the row count somehow, instead of counting the rows dynamically. Have you thought about implementing that? It would be easy for you to write your query since you'd have to add `WHERE active = 1 AND num_rows > 20` which is what you're after. – N.B. Jul 20 '15 at 13:34

2 Answers2

2

I think this is what you are after:

UPDATE MyTable
SET active=2
WHERE active=1
  AND (SELECT COUNT(*) FROM (SELECT * FROM MyTable WHERE active=2) as tbl) >20

Explanation:

This query will update active = 2 for those records which satisfy the following conditions:

  1. active=1

  2. There more 20 records in table with active=2

Result:

SQL Fiddle without updation (there are no 20 records with acive=2)

SQL Fiddle with updation (there are 20 records with acive=2)

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • Seems that you would get the error I mention in my answer with this query. – xlecoustillier Jul 20 '15 at 10:41
  • @user3815508: Updated my answer. Try it now! – Raging Bull Jul 20 '15 at 10:46
  • In the table have to remain always 20 records with active=1, and all others records e.g. the 21, 22, 23, ... must have active=2. In other words, the table must contains only 20 records with active=1. If the table contains e.g. 30 records, then the first 20 records have to contain active=1 and others 10 records have to contain active=2. See this SQL Fiddle: [link](http://sqlfiddle.com/#!9/64eb7/1). – user3815508 Jul 20 '15 at 12:18
  • @user3815508: Can you explain a little bit more. `If the table contains e.g. 30 records, then the first 20 records have to contain active=1 and others 10 records have to contain active=2.` What if 15th record has active =2 or 25th records has active=1. How do you want it? If possible, please edit your question and add an explanation. – Raging Bull Jul 20 '15 at 12:55
  • If 15th record has active=2 it could be remain as active=2, only from (limit) 20 of data it gets interesting. But 25th records have to set active=2. (See this wrong Fiddle: [link]( http://sqlfiddle.com/#!9/64eb7/1) in order to understand it better. – user3815508 Jul 20 '15 at 13:06
  • @user3815508: The fiddle is not wrong. You just changed the query I have provided. See at the end `WHERE active=1)` In my answer, it is 2. – Raging Bull Jul 20 '15 at 13:20
  • Yes, I did it on purpose. In the case of "WHERE active = 2", it also doesn't work properly.The first 20 recods have to be active= 1, all other active=2 I have updated my question, see it please. – user3815508 Jul 20 '15 at 13:40
  • @user3815508: Then this problem will get bigger and bigger. Because, there is no difficulty in verifying that all the records has a particular value. But, without having a serial number or row number in the table, we will not get the correct output. .i.e., we cannot find "first 20 items", "20 to 30 items", so and so. The result may not be correct. There is no guarantee here. What you can do is, create a column for serial no, and then we will can create the query eventhough it will be a big query. – Raging Bull Jul 20 '15 at 13:57
1

Something like this should work:

UPDATE MyTable
SET active = 2
WHERE active = 1
AND (SELECT COUNT(*) FROM (SELECT * FROM MyTable) a) > 20

In MySQL, you have to create a subquery to circumvent the "You can't specify MyTable for update in FROM clause" error you would get otherwise. See this post for more information.

See this fiddle.

Community
  • 1
  • 1
xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
  • Tanks, but I get the following error: MySQL said: Documentation #1093 - You can't specify target table 'MyTable' for update in FROM clause – user3815508 Jul 20 '15 at 10:45
  • You shouldn't, thanks to the subquery. See the fiddle that shows that working. – xlecoustillier Jul 20 '15 at 12:23
  • Ok. There are 21 records. The first 20 must have active=1, but the 21 (or 22,23,...) must have active=2. This behavior doesn't work in your solution. – user3815508 Jul 20 '15 at 12:33
  • Ok, didn't get that from your question. So what you're trying to do is update all records except 20 to active = 2, the other remaining to active = 1 ? – xlecoustillier Jul 20 '15 at 12:37
  • What I'm trying to do, the Table MyTable always takes records on. If the number of records will be greater than 20 (more than 20 records), then the older records (e.g. 21, 22, 23, ...) have to set "active = 2". But always the first 20 newly records hava active=1. – user3815508 Jul 20 '15 at 12:46
  • Then you'll have to find a way to identify your 20 latest records, because when you'll add a row, you'll have to know which of the 20 active1 recors has to be set to active = 2. You cannot rely only on the rownum to do that. With a timestamp or something like that you could select the 20 most recent records and set the other to active=2. – xlecoustillier Jul 20 '15 at 12:54