Re: [sqlite] Updating multiple records in C#

[Richard Andersen]

Wednesday, December 7, 2016, 9:15:57 AM, you wrote:

Thanks Ryan, this looks very useful, think I've got what I need now to
make it work!

Richard

>> 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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating multiple records in C#

[Richard Andersen]

Wednesday, December 7, 2016, 3:12:05 AM, you wrote:


>> On Dec 6, 2016, at 5:40 PM, Richard Andersen  wrote:
>> 
>> cmd.CommandText = @"UPDATE pdata SET FileName = @fileName WHERE FileName = 
>> 'filename.zip'";

> You can add other columns to set by adding more “name = value”
> expressions in between SET and WHERE.
> Here’s the documentation of the UPDATE command:
> http://www.sqlite.org/lang_update.html
> 

Ah - have already looked at these pages but ignored the diagrams as I
thought they were refering to some internal technical stuff, but now I
realize they're explaining the syntax (never seen it done that way
before). 

Thanks!
Richard

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating multiple records in C#

[R Smith]

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

Re: [sqlite] Updating multiple records in C#

[Jens Alfke]

> On Dec 6, 2016, at 5:40 PM, Richard Andersen  wrote:
> 
> cmd.CommandText = @"UPDATE pdata SET FileName = @fileName WHERE FileName = 
> 'filename.zip'";

You can add other columns to set by adding more “name = value” expressions in 
between SET and WHERE.
Here’s the documentation of the UPDATE command: 
http://www.sqlite.org/lang_update.html 

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users