Geek Answers Handbook

The set of identical identifiers in the expression WHERE id IN ()

I have a simple query that increases the value of the field by 1. Now I used a loop for all id and ran a query for each of them, but now that everything becomes a bit resourceful, I would like to optimize this. I usually just did

UPDATE table SET field = field + 1 WHERE id IN (all the ids here)

but now I have a problem that there is an identifier that happens twice (or more, I can’t know what is on forehand). Is there a way for the request to be executed twice for id 4 if the request looks like this:

UPDATE table SET field = field + 1 WHERE id IN (1, 2, 3, 4, 4, 5)

Thank,

lordstyx

Edit: Sorry for not being clear enough. The identifier here is the auto inc field, so these are all unique identifiers. the identifier that needs to be updated is indirectly coming from users, so I cannot predict which identifier will occur, how often. If there is an ID (1, 2, 3, 4, 4, 5), I need the row field with id 4 to increase by 2, and everything else to 1.

+5
source share
5 answers

If (1, 2, 3, 4, 4, 5)coming from a request SELECT id ..., you can do something like this:

UPDATE yourTable 
  JOIN 
    ( SELECT id
           , COUNT(id) AS counter 
      ....
      GROUP BY id
    ) AS data
    ON yourTable.id = data.id
SET yourTable.field = yourTable.field + data.counter 
;

Since the input comes from users, maybe you can manipulate it a bit. Change (1, 2, 3, 4, 4, 5)to (1), (2), (3), (4), (4), (5).

Then (by creating a temporary table):

CREATE TABLE tempUpdate
( id INT )
;

Perform the following procedure:

  • ,
  • .

:

INSERT INTO TempUpdate
VALUES (1), (2), (3), (4), (4), (5)
;

UPDATE yourTable 
  JOIN 
    ( SELECT id
           , COUNT(id) AS counter 
      FROM TempUpdate
      GROUP BY id
    ) AS data
    ON yourTable.id = data.id
SET yourTable.field = yourTable.field + data.counter 
;

DELETE FROM TempUpdate
;
0

. -

UPDATE table
   SET field = field + (LENGTH(',1,2,3,4,4,5,') - LENGTH(REPLACE(',1,2,3,4,4,5,', CONCAT(',', id, ','), ''))) / LENGTH(CONCAT(',', id, ','))
 WHERE id IN (1, 2, 3, 4, 4, 5)

id = 4,

0

, , , , .

, test. id. idwas, , id :

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `idwas` int(8) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ;

:

truncate table test;
insert into test(id) VALUES(1),(3),(15);
update test set idwas = id;

, 1,3,5,3, :

  • id 1 1
  • id 3 2
  • id 5 , .
  • 15 ,

, :

SET @userInput = '1,3,5,3';

:

SET @helperTable = CONCAT(
  'SELECT us.id, count(us.id) as i FROM  ',
  '(SELECT ',REPLACE(@userInput, ',',' AS `id` UNION ALL SELECT '),
  ') AS us GROUP BY us.id');

SET @stmtText = CONCAT(
  ' UPDATE  ',
  '(',@helperTable,') AS h INNER JOIN test as t ON t.id = h.id',
  ' SET t.id = t.id + h.i');

PREPARE stmt FROM @stmtText;

EXECUTE stmt;

:

mysql> SELECT * FROM test;
+----+-------+
| id | idwas |
+----+-------+
|  2 |     1 |
|  5 |     3 |
| 15 |    15 |
+----+-------+
3 rows in set (0.00 sec)
0

, , , , .

, , ( ) - . , , , , , , : (increment-number = > ), , ,

UPDATE table SET field = field + 1 WHERE id IN (1, 2, 3, 5)
UPDATE table SET field = field + 2 WHERE id IN (4)

php, , , , (id = > count), (count = > ).

, , . , , , SQL. SQL ( , ), , , .

0

:

create temporary table temp1 (id integer);
insert into temp1 (id) values (1),(2),(3),(4),(4),(5);
update your_table set your_field = your_field + (select count(*) from temp1 where id = your_table.id)

(1), (2), (3), (4), (4), (5), , , ? , , !

,

0

More articles:


All Articles