0

I'm confused on how I seem to fail at doing a simple update in SQLite. I've installed SQLite Manager in Firefox and I want to update the moz_places table there because we recently changed 'provider' and instead of clearing my whole history in Firefox I thought it would be nice if I could simply update the information so the history points to the new location.

The idea is to run this code:

UPDATE moz_places SET url = REPLACE(url, '.old.com/', '.new.com/')

When I press [Run SQL] I've got the impression that nothing happens even though the Last Error field shows 'not an error'. Maybe it's simply very fast? Nope, when I then go into the data I find that it did not update a thing and my places folder is still full with old.com urls.

However, when I try

UPDATE moz_places SET url = REPLACE(url, '.old.com/', '.new.com/') WHERE id = 2458

it DOES update this one record.

Naively assuming that the SQLite syntax requires a WHERE I then added 

UPDATE moz_places SET url = REPLACE(url, '.old.com/', '.new.com/') WHERE id > 0

but that didn't get me any results either.

Does SQLite only allow singe-row updates? Looking around on the internet I do not see such a limitation, but otherwise I'm not sure what could be the issue.

PS: I'm doing this on a copy of the /Profiles/ folder I found in %appdata%, not directly on the 'live' version. So locking should not be an issue either.

deroby
  • 5,902
  • 2
  • 19
  • 33
  • Checking my post for spelling errors I now suddenly come to wonder if this could be due to a unique constraint/index I'm triggering... – deroby Mar 09 '15 at 10:59

2 Answers2

0

OK, stupid me.

Turns out this failed because of the fact that I already had some records in there with the new.com url and my UPDATE would then cause conflicts.

I've solved it by going this route:

UPDATE moz_places 
   SET url = REPLACE(moz_places.url, '.old.com', '.new.com') 
 WHERE NOT EXITS ( SELECT *
                      FROM moz_places o 
                     WHERE o.url = REPLACE(moz_places.url, '.old.com', '.new.com') )

It did take while to finish; making SQLite Manager unresponsive, but in the end it now shows things as I had hoped they would be.

deroby
  • 5,902
  • 2
  • 19
  • 33
0

The update query does not work. The only solution is to,

  1. Write the Update query, eg. 

    UPDATE table_name SET "col_name" = 'Anand Goudar' WHERE "col_id" = 'Something'

  2. After the query click enter List item

  3. Hover on File(Top Left) and click on Download Active Database
  4. Save the file and this file will have the updated information.

Usually the assumption is, when the query is executed the table row must change, but here it does not happen. So for each Update/Insert/Delete query you need to Download Active Database.

Poor stuff, but I am sure they(Sqlite manager) will come up with a fix

deroby
  • 5,902
  • 2
  • 19
  • 33
Anand Goudar
  • 51
  • 4
  • I realize I had to refresh the data to see the new values; but the issue was more about the fact that the query failed without giving me a proper indication as to why. As seen in my own answer, the result of the query would clash with a unique constraint. By working around that the `UDPATE` went fine and indeed was visible after a refresh. – deroby Feb 01 '19 at 13:57