On 2014/05/24 12:08, Humblebee wrote:
I'm making good headway with the conversion from the use of strings
into a normalized database. With the kind help of everyone on the
list, I can retrieve the data from the new structure.
I am now at the stage of saving the data to the new structure. Each
time I do an update, I need to update multiple rows in the
TeamPersonTable with a new orderId and TeamId. I looked up the SQLite
documentation and found that Insert works on multiple rows. Can I
update multiple rows with one statement ?
Not in the way that Insert works (as others have already mentioned) and yes, updating many things when done inside a transaction is
much faster as Simon suggested.
It's not impossible though - and if you have a real reason to do multiple updates a lot and would find it much better to update in
list-like fashion as per the Insert method, there is a way, it simply involves making either a primary key or Unique index and then
using inserts.
So let's say your TeamPersonTable looks like this:
tpId, teamId, personId, orderId
1 : 1 : 4 : 1
2 : 1 : 5 : 0
3 : 2 : 4 : 0
4 : 2 : 5 : 1
where tpId is the Primary Key (or other form of Unique Index) you can construct the following Query to update the team and order
values (The new teams are say 3 and 7 and the new orders are reversed):
INSERT OR UPDATE TeamPersonTable (tpId,teamId,personId,orderId) VALUES
(1,3,4,0),(2,3,5,1),(3,7,4,1),(4,7,5,0);
Basically this will try to insert the new value, but because the primary key already exists in the table, it will "fail" the Insert,
and fall back to (or handle the error by) using the "Update" method (as per our SQL) and will simply update (overwrite) the existing
row where the primary key is the same.
The downside to this method (and why Updates are better) is that you have to supply the ENTIRE row's values every time - where, with
UPDATE, you only need to supply (or know) the field(s) you are actually updating. Moreover, when you later change the table's layout
(and you likely will), you will have to change all the INSERT queries but not necessarily the UPDATE queries since they only target
the fields specified and not the entire row. I know in this table it's probably of little concern, but bigger tables will be really
cumbersome using the method above.
Achieving what we did up there with the inserts, but using updates might look like this - a bit more SQL, but easier to follow and
adjust later:
BEGIN TRANSACTION; -- or just BEGIN;
UPDATE TeamPersonTable SET teamId=3 WHERE teamId=1;
UPDATE TeamPersonTable SET teamId=7 WHERE teamId=2;
UPDATE TeamPersonTable SET orderId=0 WHERE tpId IN (1,4);
UPDATE TeamPersonTable SET orderId=1 WHERE tpId IN (2,3);
COMMIT; -- or END TRANSACTION; or just END;
obviously your own criteria for deciding what the Ids must be will make the
actual SQL look different, but you get the idea.
A note on transactions: The INSERT OR UPDATE thing above is just one statement and as such the transaction is implicit - i.e. SQLite
treats it as a transaction, as if we did a start and end before and after it anyway and if one part of it fails, everything gets
undone. The later UPDATE functions are multiple, and so enclosed in an explicit transaction - i.e. where we tell it when to start
and end.
This explicit transaction (in the way I have done it) mainly increases speed, it won't actually undo anything - since there is no
ROLLBACK specified (unless the computer crashes mid-transaction in which case it will be rolled back automatically next time you
open a connection to it). You may (if needed) add this kind of functionality for the processing of transactions with specifying how
to handle errors and the like, which you can read more about in the transaction pages on the SQLite site.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
