Unable to update desired rows

Question

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?

Run a profiler and figure out how does the query execute, here issue seems with the parameter binding from the C# client, somewhere its not getting the correct value or value is being overridden, Otherwise if hardcoding works, then nothing much wrong with query as such — Mrinal Kamboj, Feb 08 '18 at 11:38

cosh · Accepted Answer · 2018-02-08T13:01:19.617

As you said, I don't know if you can use IN like that, i.e. using a variable. Anyway, a simple workaround would be to use a temporary table to store information between the two queries:

CREATE TEMPORARY TABLE temp(
   sightinguid #typeofsightinguid 
)  

INSERT INTO temp
SELECT //select1
    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 (SELECT sightinguid FROM temp);

DROP TABLE temp;
SELECT @out_param;

If temporary tables are not an option (whatever the reason), then you're gonna have to do something like suggested here or here: basically, limit a subquery of the subquery. Like:

UPDATE pc
SET 
    last_accessed_timestamp = NOW()
WHERE
    sightinguid IN (
        SELECT sightinguid FROM (
            SELECT //select2
               sightinguid 
            FROM pc
            INNER JOIN c 
            ON uid = id
            WHERE //... other conditions
            LIMIT 50
        ) tmp
     )

Also, one more thing of note that I forgot to mention previously: using LIMIT without ORDER BY can result in non-deterministic queries, i.e. with different row order. So, following the example I wrote, you COULD get 2 different result sets on select1 and select2.

Thanks just tried it out and it seems to work. Any way to optimise it though? It's taking way too long — MathLover, Feb 08 '18 at 13:08
That's too broad of a question. There are many different approaches to optimizing query performance, and many of them don't even relate to query syntax. That warrants you post another question, with more details and specificity. — cosh, Feb 08 '18 at 13:19

Unable to update desired rows

1 Answers1