How do I get the rows from sql select to use them afterwards in the same transaction?-CodePudding

Suppose we are doing a SELECT and UPDATE in the same commit in mysql (MariaDB). How can I do:

SELECT id from my_table WHERE mycondition LIMIT 20 FOR UPDATE; 
UPDATE my_table SET column1 = 0 WHERE id = the result of the previous select
COMMIT

where id is a primary key auto increment column.

EDIT: I understand that doing like this I would get the result of the SELECT printed, no? At least is what I wanted too, to know which rows I modified

CodePudding user response：

This does not work:

-- UPDATE my_table 
-- SET column1 = 0 
-- WHERE id in (SELECT id from my_table WHERE mycondition LIMIT 20)

edited:

SELECT @IDS:= GROUP_CONCAT(id) 
FROM my_table 
WHERE mycondition 
LIMIT 20;

UPDATE my_table
SET column1 = 0
where FIND_IN_SET(id,@IDS);

SELECT @IDS;

CodePudding user response：

I would phrase this as a single update join:

UPDATE my_table t1
INNER JOIN
(
    SELECT id
    FROM my_table
    ORDER BY <something>
    LIMIT 20
) t2
    ON t2.id = t1.id
SET
    column1 = 0;

Note that using LIMIT without ORDER BY is fairly undefined and meaningless. If you want to select 20 records using LIMIT, it should be with respect to a certain ordering of your table.