0

I'm attempting to run a query that does a few things..

  1. Create a variable for updated ID's
  2. Update all jobs from the jobs table where status = 0
  3. Select all of those updated jobs.

The reason behind this, is there are multiple node js clusters querying the database and I can not have any scenario in which two of them read the same jobs as the job will be processed twice and that's bad.

I have it almost there. It's running however it's not returning anything, even though it's telling me:

2 row(s) affected Rows matched: 2 changed: 2 warnings: 0

Here is a simple version of the table

CREATE TABLE test (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `status` INT(4) NOT NULL,
  `task` INT(4) NOT NULL,
  PRIMARY KEY (`id`));

Lets put some data in the table..

INSERT INTO test (status, task)
VALUES (1, 1), (0, 1), (0, 2), (3, 1);

So now we have a 'test' table with 4 jobs. The status indexing looks like this..

0 = unprocessed
1 = processing
3 = completed

The output of my query should be to update rows 2 and 3 from status 0 to a 1, then use those ID's retrieved in a select statement to get the rest of the data.

Here is my query:

BEGIN;
SET @LastUpdateID = NULL;
UPDATE test
    SET status = 1
    WHERE status = 0
    AND (SELECT @LastUpdateID := CONCAT_WS(',', id, @LastUpdateID));
SELECT * from test where id in (@LastUpdateID);
COMMIT;

Now what's weird, is it's all running correctly, it's just not returning rows from the SELECT * statement. I even checked the output of the variable @LastUpdateID and that looks like: '2,3'

Here is a screenshot of what I get in Workbench and I've also confirmed the same results from command line.

screenshot of mysql workbench not returning data

Here is the kind of thing I would expect. screenshot of mysql workbench displaying correct data

Nicholas Mordecai
  • 859
  • 2
  • 12
  • 33

1 Answers1

0

It look like somebody already answer MySQL: return updated rows

Look like, the answer is the same as you already do ...

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

Try to remove the begin/commit

vincent PHILIPPE
  • 975
  • 11
  • 26