CODEWITHSUNDEEP
sqliteionic2
ACES
ACES

Reputation: 490

How to write insert query for SQLite with selected values

I am using Ionic 2 and SQLite trying to insert a comment in the table and point_id to be equal to second highest point_id of particular match. So I wrote following query, but Ionic 2 is throwing me 

sqlite3_prepare_v2 failure: no such column: match_id

INSERT INTO comments (point_id, match_id, comment, created_at)
VALUES((
   SELECT name.point_id 
   FROM (
       SELECT point_id from undo 
       WHERE match_id = ? 
       ORDER BY point_id DESC 
       LIMIT 2) AS name 
   WHERE match_id = ? 
   ORDER BY point_id LIMIT 1), ?, ?, ?)

My whole ionic 2 code is 

NativeStorage.getItem('matchID').then(matchID => {
          let query = 'INSERT INTO comments (point_id, match_id, comment, created_at) '+
                      'VALUES((SELECT name.point_id FROM (SELECT point_id from undo WHERE match_id = ? ORDER BY point_id DESC LIMIT 2) AS name WHERE match_id = ? ORDER BY point_id LIMIT 1), ?, ?, ?)';
            this.db.executeSql(query, [matchID.value, matchID.value, matchID.value, data.comment, new Date()]).then(data_comment => {
              let toast = this.toastCtrl.create({
                message: 'Comment was added successfully',
                duration: 2000,
                position: 'top'
              });

              toast.present();
              console.log("comment entered", data_comment);
            }, err => console.error("ERROR enter comment in match tracker ", err))
          })

Thank you very much.

Upvotes: 0

Views: 188

Answers (1)

ACES
ACES

Reputation: 490

I changed my SQLite query and now it works fine. If somebody else needs this here it is:

INSERT INTO comments (point_id, match_id, comment, created_at)
VALUES((
    SELECT point_id from undo 
    WHERE match_id = ? 
    ORDER BY point_id DESC 
    LIMIT 1 OFFSET 1), ?, ?, ?)

Upvotes: 1

Related Questions