Unable to update desired rows
Hi,I have the following query which is executed in a single string through C#.
SET @out_param := '';
SELECT
sightinguid
FROM
pc
INNER JOIN
c ON uid = id
WHERE
//... other conditions
AND (@out_param:=CONCAT_WS(',', sightinguid, @out_param))
LIMIT 50 FOR UPDATE;
UPDATE pc
SET
last_accessed_timestamp = NOW()
WHERE
sightinguid IN (@out_param);
SELECT @out_param;
I am basically trying to put the first 50 values of the first query in a comma separated string, and return this string at the end. Before doing so, I would like the update statement to execute on those same records. However, only the very first sightinguid is being updated. When I hardcode multiple values in the sightinguid IN (@out_param) part it works and updates them all - so I am assuming there is something wrong with that part.
I cannot put the SELECT in a subquery and update from there, due to the LIMIT 50 part, since MySQL does not let you put a LIMIT in a subquery.
Any ideas?
Thank You
kosmiktechnologies.com