How to write insert query for SQLite with selected values

Question

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.

ACES · Accepted Answer · 2016-12-28 19:16:05Z

1

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), ?, ?, ?)

answered Dec 28, 2016 at 19:16

ACES

5004 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write insert query for SQLite with selected values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related