On 2016/12/07 3:40 AM, Richard Andersen wrote:
I have a table with several thousand records each with multiple
fields. One field contains a filename, this is the only unique field
so I use it to identify the different records.
What I need is a way to update all fields that have changed in any
record, at once. There's no need to check IF a record has changed,
that's already been done; I have all the data for the changed records
in a jagged array and just need to update the corresponding table
records, using the file name as record identifier. I know how to do
this for one record and one field, in this case the file name field:
using (SQLiteConnection con = new SQLiteConnection(_connectionString))
{
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = @"UPDATE pdata SET FileName = @fileName
WHERE FileName = 'filename.zip'";
cmd.Connection = con;
cmd.Parameters.Add(new SQLiteParameter("@fileName", "newfilename.zip"));
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
But how do I do this for multiple records and mutiple fields? I
imagine I need to use SQLiteTransaction here but I'm not sure about
the syntax.
It depends what kind of updates they are. If you want the same fields to
be updated in the same way, you can simply add more update fields and
expand the WHERE clause to include all the afflicted records, like this:
UPDATE pdata
SET FileName = @filename, FileNumber = @filneo, SomeOtherField = @
someotherfld
WHERE FileName IN ('example1.zip', 'example2.zip', 'example3.zip');
If however the updates that should be done are different for each row,
you have to do them one by one matching the correct update values
against the correct WHERE clause filter so that only the rows matching
that specific filter are affected. You CAN do this in an sql script /
list normally, starting a transaction and adding all the different
UPDATE commands to the list (separated by semi-colons) and running it
all at once - although I think your data interface might not allow that,
and you cannot bind into a list, so you have to populate the strings first.
If executing a list /is/ allowed, you can do this for example:
Do a BEGIN TRANSACTION first, if it succeeds, compile the following kind
of list:
------------
UPDATE pdata SET FileName = 'NewFilename1.zip', OtherFIeld = '1' WHERE
FileName = 'example1.zip';
UPDATE pdata SET FileName = 'NewFilename2.zip', OtherFIeld = '2' WHERE
FileName = 'example2.zip';
UPDATE pdata SET FileName = 'NewFilename3.zip', OtherFIeld = '3' WHERE
FileName = 'example3.zip';
------------
Execute the list, and if it succeeds, do a COMMIT; - else do a ROLLBACK;
and report the error somewhere.
Good luck.
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
