Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Comments on In MySQL is there a limit to the number of keys in a IN() clause?
Parent
In MySQL is there a limit to the number of keys in a IN() clause?
I have a PHP program that does a SELECT and then updates some of the values based on an algorithm.
Rather than updating one row at a time
UPDATE example_table
SET COLUMN_A = 1
WHERE primary_key_column = 10;
I was thinking of doing many updates at once like
UPDATE example_table
SET COLUMN_A = 1
WHERE primary_key_column IN(1,2,3,4,5);
The SELECTS are pulling in 100,000 rows at a time and to start with every single one of them may need to be updated, am I going to run into a limit of values in the IN() clause?
Post
I'll readily admit I'm not too familiar with MySQL specifically, but personally, I would try to avoid listing all the primary key values in an ad-hoc query.
What I would rather do personally is to run a separate query to select the rows to update, and then include a condition that the rows to be updated are those that exist in that set of rows.
Something not entirely dissimilar to
UPDATE example_table
SET column_A = 1
WHERE primary_key_column IN (
SELECT primary_key_column
FROM example_table
WHERE <bunch of complex conditions>
)
Of course, in this particular case, you could just as well stick the WHERE clause from the subquery in the UPDATE statement itself, but that gets somewhat more complex if the query to find the rows to update is more complex than a plain SELECT from the same table that you're updating; say, it's actually selecting from a different table, with its own joins, maybe a union or two, and so on.
If you need to use the same set of key values multiple times and the query to find them is non-trivial, stick them into a temporary table that you SELECT from (or join against) instead, and put all of that into a stored procedure.
1 comment thread