SELECT INTO - WebSQL - How to?

Databases
1

This is a part of a chrome extension.
I want to select the names , id and urls for some bookmarks and Bookmark folders. Store this info into tables - bookmarkSync and groupSync. Check an existing table(bookmarks and groups) if there is a record for a bookmark or folder(number of visits, etc). Replace tbookmarks with bookmarkSync and groups with groupssync.

speeddial.storage.Sync = function() {
  chrome.bookmarks.getChildren(String(localStorage['rootFolderID']), function(newSync){
    speeddial.storage.db.transaction(function(tx){
      tx.executeSql('DELETE FROM bookmarksSync',null,null,speeddial.storage.onError);   
      tx.executeSql('DELETE FROM groupsSync',null,null,speeddial.storage.onError);  
      for (var i=0; i<newSync.length; i++){
    if(!newSync[i].url)
    {
      tx.executeSql('SELECT INTO groupsSync FROM groups', [],null
      ,speeddial.storage.onError);
    }
    ...
    //above is the end of else statement
      }
    })
  })
}

I want to use SELECT * INTO to copy some values from one SQL table to another. When I use the above I get near INTO syntax error. I WANT TO NOW HOW TO DO BOTH THINGS - copy the values of some columns form table A to table B based on a specific column value, and completely replacing the contend of table A with this of table B.

2

INSERT INTO table1
SELECT
… fields here …
FROM table2
WHERE …

3

I did it. But javascript did all the work…

4

This is the most natural answer but there are some javascript problems that limit me. But this is not a part of this conversation:) If I can successfully pass some variables I will be able to do it:)

5

About testing my queries - I should find a way to do that - as this is an extension for chrome, I can see the results into nicely formatted tables, if that is what I mean. I will google how to it.
I dont like the other part of the answer, though - but there have to be another way:) Anyway, thanx for the help - I know only what I want to do - but not how, not even how it is named (joined update)

6

How about doing it all when you insert the new data?


tx.executeSql('
INSERT INTO bookmarksSync 
SELECT 
    ?, ?, ?, ?
  , thumbnail
  , ts_created
  , visits
  , visits_morning
  , visits_afternoon
  , visits_evening
  , visits_night
  , position
  , idgroup
FROM bookmarks 
WHERE bookmarkID=?' 
 , [newSync[i].title,  newSync[i].id, newSync[i].url, 0,  newSync[i].id],null,speeddial.storage.onError);

You will have to put the selected values in the correct order.

7

ah, okay, my bad – i did not know that sqlite uses AUTOINCREMENT

i did know, however, that mysql uses AUTO_INCREMENT, and i should’ve spotted the difference

so you did post in the right forum :slight_smile:

anyhow, what i said still holds, you should be testing your queries right in the database, not via javascript (if that’s what that is)

okay, to your problem…

i don’t use sqlite, but a quick google reveals that you’re going to have to look for some other way to do what you’re doing, because sqlite does not support a joined update i.e. where you match rows from two tables on some column, and then copy values across from one row to the other

http://stackoverflow.com/questions/773441/how-do-i-make-an-update-while-joining-tables-on-sqlite

8

Am I ? Sorry. , but I am really new to all this. I am Modding and existing chrome extension for my own needs, learning how to do it on the fly with no experience in programming. The other language is JavaScript. This should be WEBSQL, which should be SQLITE variation this is why I posted here. :slight_smile:

9

whoa, you’re using mysql – you shoulda posted in the MySQL forum

please note: the Databases forum is for all databases other than mysql

we’ll get your thread moved, don’t worry :slight_smile:

in the meantime, what front end tool are you using? you should be testing your queries, and running your CREATE TABLE queries, directly in mysql, not via that language, whatever it is (perl?)

10 
	speeddial.storage.createTable = function(callback) {
	  speeddial.storage.db.transaction(function(tx) {
	    tx.executeSql('CREATE TABLE IF NOT EXISTS ' + 
	      'bookmarks(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT, url TEXT, thumbnail TEXT NULL, ts_created INTEGER, visits INTEGER, visits_morning INTEGER, visits_afternoon INTEGER, visits_evening INTEGER, visits_night INTEGER, position INTEGER, idgroup INTEGER, bookmarkID INTEGER)', [],
	      callback,speeddial.storage.onError);
	  });
	}
// 	CREATE TABLE TO DUMP BOOKMARKS INTO
	speeddial.storage.createTableSync = function(callback) {
	  speeddial.storage.db.transaction(function(tx) {
	    tx.executeSql('CREATE TABLE IF NOT EXISTS ' + 
	    'bookmarksSync(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT, url TEXT, thumbnail TEXT NULL, ts_created INTEGER, visits INTEGER, visits_morning INTEGER, visits_afternoon INTEGER, visits_evening INTEGER, visits_night INTEGER, position INTEGER, idgroup INTEGER, bookmarkID INTEGER)', [],
			  callback,speeddial.storage.onError);
	  });
	}

This is how both tables are created.

Some values are added to bookmarkSync here

tx.executeSql('INSERT INTO bookmarksSync (title, bookmarkID, url, idgroup) values (?, ?, ?, ?)', [newSync[i].title,  newSync[i].id, newSync[i].url, 0],null,speeddial.storage.onError);

I want , from here on - to update the other 9 columns, where the the coumn with name bookmarkID has the value [newSync[i].id].

that is what I tried:

tx.executeSql('INSERT INTO bookmarksSync SELECT thumbnail, ts_created, visits, visits_morning, visits_afternoon, visits_evening, visits_night, position, idgroup FROM bookmarks WHERE bookmarkID=? ', [newSync[i].id],null,speeddial.storage.onError);

t
All other rows should be left unchanged. All other rows, where bookmarkID in bookmarkSync does not match with a value in bookmarks should be left unchanged :slight_smile:
I hope this info is enough
ps THANK YOU

11

we can help you, but you’ve got to give us specifics

start by listing all the columns in both tables, then tell us which ones you want to update

12

OK, deleting the table before inserting into from the other one solves the problem.
What I fail to do is I want to update 9 of the columns of bookmarksSync with values from bookmarks if their match their BOOKMARKID value.
I tried insert into bookmarkssync select 9 names of colums here from bookmarks where BOOKMARKID =.
the error I got is that I have specified 9 columns but the table has 13. I have to use update probably, but replacing insert with update gave me syntax error.
I am new to this, this is obvious probably :slight_smile:

13

it seems you have violated a constraint :smiley:

you have an IDENTITY column or something, maybe? a UNIQUE column?

unfortunately we can’t see your table constraints from here

14

Almost there, I think
Here is what I tried

x.executeSql('INSERT INTO bookmarks SELECT * FROM bookmarksSync', [],null,speeddial.storage.onError);

The first row of bookmarkSync is added, and then the error message is fired 4 times( I have 5 entries in the table.
I got constraint failed error message, after the first row is updated, added. What might be the problem?