8 Replies - 408 Views - Last Post: 17 April 2018 - 05:37 AM

#1 Pogaca   User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 142
  • Joined: 24-August 13

Sqlite select and update problem

Posted 16 April 2018 - 12:52 AM

So, I create table named `template` and some columns in it. Afterwards i want to insert new row, but not new rows each time. Here is my code so far:

db.transaction(function(tx){
       tx.executeSql("CREATE TABLE IF NOT EXISTS template (id INTEGER NOT NULL PRIMARY KEY, colors BLOB, fonts BLOB, coordinates BLOB, activeFields BLOB)");       
   });
    
   db.transaction(function(tx){
       tx.executeSql("INSERT INTO template VALUES(?,?,?,?,?)",[null, colors, fonts,coordinates,activeFields]);
    });


I would like to create a query that will update only first row, because I don't need 1000 unused rows since only last one is taken for use. Anyone have idea how to do that?

Is This A Good Question/Topic? 0
  • +

Replies To: Sqlite select and update problem

#2 andrewsw   User is online

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,274
  • Joined: 12-December 12

Re: Sqlite select and update problem

Posted 16 April 2018 - 12:57 AM

Where do these 1000 rows come from?

"Afterwards i want to insert new row, but not new rows each time. " You need to clarify this statement. When would you be inserting and when would you be updating?
Was This Post Helpful? 0
  • +
  • -

#3 Pogaca   User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 142
  • Joined: 24-August 13

Re: Sqlite select and update problem

Posted 16 April 2018 - 01:14 AM

So, first time when I click on button (save template), table is created which is fine. Also one row is added at the same time in the table. Now, every time afterwards when I click save template, he adds row after row because of the second SQL query. Even though I to load this SQL:
"SELECT * FROM template ORDER BY id DESC LIMIT 0 , 1"


So, there is no update for first row, only new row. And I take last saved template, but all others that I don't need are still there.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is online

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,274
  • Joined: 12-December 12

Re: Sqlite select and update problem

Posted 16 April 2018 - 01:25 AM

It still isn't very clear to me but, reading between the lines, if you want to update an existing row then you need an UPDATE statement not an INSERT statement.

Then, with an UPDATE statement, you need a WHERE clause to identify the particular row(s) that you wish to update, otherwise all of them will be updated.

If there is only ever a single row in the table(?) then you don't need a WHERE clause.

If there is more than a single row then you need to be able to identify the row that you wish to update. If it is highest value id, for example (based on your SELECT statement) then for SQLite you need something like:

UPDATE template SET... WHERE ID in (SELECT ID FROM template WHERE ... ORDER BY id desc LIMIT 1)


borrowed from here.
Was This Post Helpful? 0
  • +
  • -

#5 Pogaca   User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 142
  • Joined: 24-August 13

Re: Sqlite select and update problem

Posted 16 April 2018 - 01:41 AM

Okay, let me try to explain better.User clicks on button remember template. Then I take all coordinates, font values, all colors, create JSON string and put inside table that is created that moment for the first time. He can load last template after that, and that will be the first row because he just saved one template. But, but , but. When he changes anything, or doesn't change, and yet clicks on remember template, new row will be inserted in table because that's what my code do now. I would like to check if there is already a row inside the table and if there is, I will check what was changed and having that in mind, update the first row. But I want to avoid new rows because that can go and go.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is online

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,274
  • Joined: 12-December 12

Re: Sqlite select and update problem

Posted 16 April 2018 - 02:32 AM

Quote

"...if there is already a row ", "...update the first row"

Will there only ever be a single row? If so, then, as described, you can use an UPDATE statement without a WHERE clause.

If there might be more than one row then you could datestamp a new row and use this datestamp (the most recent date/time) to isolate the row to update. This is preferable to use the id.

An alternative is to retrieve the id when creating a new row, and use this retained value when performing the update.

Bottom line is, as I've described, that you need an UPDATE statement to update an existing row; INSERT will create new row(s).



If the table will only ever have a single row then this is not necessarily the best approach to take. You could save "settings" as a simple text file perhaps, or serialized JSON or XML. Or even in a cookie or localStorage.

If, however, the settings are "per user" then the user id should, presumably, be part of the storage and retrieval from a database.
Was This Post Helpful? 1
  • +
  • -

#7 Pogaca   User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 142
  • Joined: 24-August 13

Re: Sqlite select and update problem

Posted 17 April 2018 - 12:16 AM

I think I will use this idea: Insert new row, delete the one before him. With select query pick last, first (all the same) row despite id number.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is online

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,274
  • Joined: 12-December 12

Re: Sqlite select and update problem

Posted 17 April 2018 - 12:45 AM

If you take that approach then it will be easier to delete all rows, then insert a new one. If you later perform an update you won't need a where clause as you will be updating all of them: a single row.

Anyway, good luck.
Was This Post Helpful? 0
  • +
  • -

#9 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7501
  • View blog
  • Posts: 15,544
  • Joined: 16-October 07

Re: Sqlite select and update problem

Posted 17 April 2018 - 05:37 AM

At that point, why bother with a database at all? A single row all the time seems pointless in the context of an engine designed to query data. Make a json file and just write over it.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1