Reputation: 1
I have a DataTable with around 500K+ records, and I would like to update a field in the database for each of those records contained in the DataTable.
At the moment, I am getting a list of all the records in the ID column of the DataTable and creating an update statement with an SQL in
statement on those IDs, like so:
string sql = "UPDATE my_table SET my_field = @timestamp WHERE id IN (" + String.Join(", ", myDataTable.AsEnumerable().Select(r => r.Field<Int64>("id")).ToList().ToArray()) + ")";
This works for a few records, however due to the large amount of IDs, I am now getting an error saying that the packet size in MySQL is not large enough for this query.
What can be done, besides increasing the packet size, which seems to be a bad approach?
Upvotes: 0
Views: 162
Reputation: 591
For similar task I used the following approach once:
session_id GUID
, record_id bigint
.UPDATE
primary table using INNER JOIN
to this table, specifying particular session id.DELETE
all records from session table by session id.When it's done you may start working on performance:
DROP TABLE
has to work much faster then DELETE FROM
, but in my case creating table worked too slow so I preferred using permanent table);bigint
instead of GUID
-s for identifying sessions as they have to be compared faster;COPY FROM
(PostgreSQL) or BULK INSERT
(MS SQL) statement instead of series of INSERT INTO
-s. I didn't find anything similar for MySQL though.I don't know what will suit you best )
Upvotes: 1
Reputation: 5040
Assuming id_list_table contains a list of IDs to update in my_table:
UPDATE my_table
SET my_field = @timestamp
JOIN id_list_table
ON my_table.id = id_list_table.my_table_id
Upvotes: 0
Reputation: 1507
I don't know if it is possible to increase the packet size in MySQL, but there is a simple solution to your problem by dividing your UPDATE query to multiple UPDATE queries for multiple groups of IDs
Upvotes: 0