CODEWITHSUNDEEP
mysqlsql
malefstro
malefstro

Reputation: 149

Mysql, select many rows and assign different values to each row transactionally

I have a simple MySQL 8 table like this:

CREATE TABLE IF NOT EXISTS `test_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `segment_id` int(11) unsigned NOT NULL,
  `code_id` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
);

The table is populated with some random values

INSERT INTO `test_codes` (`segment_id`) VALUES ('1');
INSERT INTO `test_codes` (`segment_id`) VALUES ('1');
INSERT INTO `test_codes` (`segment_id`) VALUES ('1');
INSERT INTO `test_codes` (`segment_id`) VALUES ('1');
INSERT INTO `test_codes` (`segment_id`) VALUES ('1');
INSERT INTO `test_codes` (`segment_id`) VALUES ('1');
INSERT INTO `test_codes` (`segment_id`) VALUES ('2');
INSERT INTO `test_codes` (`segment_id`) VALUES ('2');
INSERT INTO `test_codes` (`segment_id`) VALUES ('2');
INSERT INTO `test_codes` (`segment_id`) VALUES ('1');
INSERT INTO `test_codes` (`segment_id`) VALUES ('1');

My applications has the following task: A request comes with an array of codes eg [1,200,10,18] and I need to get 4 rows (equal to array size) from the database where the code = 0 and update the code_id at each row with the values 1,200, 10, 18 transitionally.

A concurrent request that wants to update the code from another running thread, should not access the selected rows of the first thread.

How can I do this?

After the update the first selected row will have code_id 1, the second selected row code_id 200, the third 10 and the last one 18. In other words the task must find rows with unassigned codes (code_id=0) and set a value to each row.

Link: http://sqlfiddle.com/#!9/60e555/1

Upvotes: 0

Views: 105

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

MySQL doesn't support arrays. I would suggest that you first load the array into a table. This is just a convenience, but it is handy.

Then you can use a complex update with join to handle this:

update test_codes tc join
       (select tc2.*,
               row_number() over (order by rand()) as seqnum
        from test_codes tc2
       ) tc2
       on tc2.id = tc.id join
       (select nc.*,
               row_number() over (order by code_id) as seqnum
        from new_codes nc
       ) nc
       on tc2.seqnum = nc.seqnum
    set tc.code_id = nc.code_id;

EDIT:

You can construct the query directly from the codes:

update test_codes tc join
       (select tc2.*,
               row_number() over (order by rand()) as seqnum
        from test_codes tc2
       ) tc2
       on tc2.id = tc.id join
       (select ? as code, 1 as seqnum union all
        select ? as code, 2 union all
        . . .
       ) nc
       on tc2.seqnum = nc.seqnum
    set tc.code_id = nc.code_id;

Upvotes: 0

Serg
Serg

Reputation: 22811

You can do it with a single statement which you may build in the app

update test_codes
join
(
  select id, row_number() over (order by id) rn
  from test_codes
  where code_id = 0
) t on t.id = test_codes.id
join ( 
 -- a table of new values with their positions
 select 1 rn, 1 val union all
 select 2, 200 union all
 select 3, 10 union all
 select 4, 18
) v on v.rn = t.rn
set code_id = v.val

db<>fiddle

Upvotes: 1

Related Questions