2

I found on Stack similar questions like this How to update an entire column in sqlite? But they don't explain me my how to solve my task.

Say, I have a db with 5 columns and 5 records in it. What i need is to update the last column "date" with the values of unix time that differs by 1 sec. So i need to put values 1406820974139, 1406820974140, 1406820974141, 1406820974142, 1406820974143, 1406820974144.

How to do it using ContentValues? As i got i have to loop five times to create new ContentValues object and update one record at a time (maybe using db.startTransaction() syntax).

My question is is there a way to put all values at a time into one ContentValues object and write in them into DB? Or maybe the better way is to use rawQuery using native SQL syntax as explained in How to update an entire column in sqlite?

Community
  • 1
  • 1
kolyaseg
  • 535
  • 5
  • 13

2 Answers2

2

In theory, it would be possible to put all the values into a single SQL statement:

UPDATE MyTable
SET date = CASE _id
           WHEN   5 THEN 1406820974139
           WHEN  17 THEN 1406820974140
           WHEN  23 THEN 1406820974141
           WHEN  69 THEN 1406820974142
           WHEN 666 THEN 1406820974143
           END;

However, just creating one ContentValues object for each row is easier than constructing this command.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • So using many independent DB updates (for every row) is not so bad? – kolyaseg Sep 21 '14 at 19:58
  • Why would it be so bad? – CL. Sep 22 '14 at 06:32
  • i thought many frequent queries will slow down system and in general it is bad approach... – kolyaseg Sep 22 '14 at 10:28
  • Kolyaseg you are right with this. they are slow, but this could be a design trade off when the speed is not critical, trading in speed for simplicity of the code -> better maintainability. i am not a mobile developer myself, but it seems logical that when running on a phone battery you would try stay on the efficiency side. – call me carrot Sep 22 '14 at 12:02
  • These many queries will not be slow when they all update only a single row (and if you use a single transaction for them). – CL. Sep 22 '14 at 12:27
  • @CL, you mean single transaction for all of them or one transaction per one update query? – kolyaseg Sep 23 '14 at 12:01
0

so that we know which date should go to which row? what is the cririteria to differentiate the rows?
a relational db table is different from say an excel table. there is no implicit row order (if you always see the rows in the same order,you can consider it a kind of coincidence,you can not rely on this like you do in excel), in a db table you need to have a column(or a group of them) with unique values which you use in your queries to identify each of your records. so you need to be more clear in your question. what date should go to which record (identified by what?). there is no implicit row number, if you want it, add an autoincrement PK column. then you could for instance use something along the lines of

  UPDATE table SET column5= 1406820974140+PKcolumn

where 1406820974140 is the start date you have to choose, depending on what you are up to

call me carrot
  • 192
  • 11
  • Thanks for the answer. Actually i'm not asking what to add but how to add. What if need to insert those dates in the order that differs from order of the first column (_id). I mean if user change the order of rows and i need to fix that order by incrementing date value regardless any order in the DB. Ok, i have to rewrite my question)) – kolyaseg Sep 21 '14 at 14:48
  • i do not think we are talking about the same thing. what do you mean by "if user changes the order of rows" ? there is no order of records in the database. that was the whole point of my answer. you are probably talking about ordering the records in the GUI. it has nothing to do with the order they are then stored in the db. then your problem is not DB/sqlite related, right? – call me carrot Sep 21 '14 at 15:08