delete and insert rows mysql

Question

i have to update multiple rows (around 32rows at most) in a table. currently my code can delete the rows specified but cant insert. am i missing something? my code looks like this:

connection.query("DELETE FROM items WHERE record_id = ?", id, function (err, result) {
    if(err)
    { console.log("ERROR UPDATE" + err); }
    else
    { 
        console.log("entered UPDATE");
        // Loop through Hour Difference 
        for (var i = 1; i <= hours; i++) {

            // Avoiding Add in the first iteration
            if (i != 1) {
              start_date_time.add(1, "hours");
            }

          // Convert Date Format to MYSQL DateTime Format
          var myDate3 = moment(start_date_time.format('YYYY-MM-DD HH:mm:ss')).format("YYYY-MM-DD HH:mm:ss");
          console.log('Index update [' + i + ']: ' + myDate3);

          var data = {
              name: req.body.site_name,
              remarks: req.body.remarks,
              date_part: myDate3,
              record_id: id
          }

          connection.query("INSERT INTO items SET ?", [data], function (err, result) {
              if (err) {
                  console.log(err);
              } else {
                  console.log('Index [' + i + ']: INSERTED to update');
              }
          });
        }
    }
});

You mixing sync and async code. Check my comment above. Should help you a lot. You need to change for loop into promises. developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… Hope this helps. — Mykola Borysyuk
– Mykola Borysyuk, Commented Jul 18, 2018 at 7:04

Roshana Pitigala · Accepted Answer · 2018-07-18 07:34:36Z

1

Your insert query is incorrect. Correct syntax for an insert query is,

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

Read SQL INSERT INTO Statement.

If you are using SET it should be an update query. Correct syntax for an update query is,

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Read SQL UPDATE Statement

edited Jul 18, 2018 at 7:34

answered Jul 18, 2018 at 7:30

Roshana Pitigala

8,8569 gold badges53 silver badges87 bronze badges

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

1 Comment

Mykola Borysyuk Over a year ago

Damn i missed that query is bad, notice async/sync code mix :D

Mykola Borysyuk · Accepted Answer · 2018-07-18 07:55:17Z

0

So in your example you use asynchronous and synchronous code together which is bad idea.

Also thanks @Roshana your query is bad, so i fix it.

To fix it you can use two approaches.

Use async/await

Use Promises

So here is basic example :

Using both approaches

async function doYourQueries (id, hours, req) {
 try {
     //this will wait until query finishes correctly, otherwise it will throw error.
     let deleteData = await connection.query("DELETE FROM items WHERE record_id = ?", id);

     let insertQueries = [];
     for (var i = 1; i <= hours; i++) {
         if (i != 1) {
             start_date_time.add(1, "hours");
         }
         let myDate3 = moment(start_date_time.format('YYYY-MM-DD HH:mm:ss')).format("YYYY-MM-DD HH:mm:ss");
         console.log('Index update [' + i + ']: ' + myDate3);
         let data = [req.body.site_name,
             req.body.remarks,
             myDate3,
             id
         ];
 //in here col1, colN need to be changed to your table column names
         insertQueries.push(connection.query("INSERT INTO items (col1, col2, col3, col4) VALUES (?,?,?,?)", data));
     }
     //run promise all. Result of all queries will be in allUpdates array.
     let allInserts = await Promise.all(insertQueries);
     console.log("We should be done right now.")
 }
 catch(err) {
     console.log("We got error in execution steps");
     throw err;
 }
}

Also to run this you can just Do.

doYourQueries(id, hours, req)
  .then(res => console.log("All good")
  .catch(err => console.log("OOPS we got error");

Hope this helps.

edited Jul 18, 2018 at 7:55

answered Jul 18, 2018 at 7:28

Mykola Borysyuk

3,4211 gold badge22 silver badges24 bronze badges

3 Comments

Sydnie Over a year ago

hi, i tried your code.. it does delete the data in the table, but it doesnt insert..

Mykola Borysyuk Over a year ago

Well you need to adjust queries and data for you're scenario. Also check the column names and values that you passed. I dont know your table structure

Mykola Borysyuk Over a year ago

I modify answer slightly. You need to pass all parameters to method. In my Node JS > 8 it works.

Collectives™ on Stack Overflow

delete and insert rows mysql

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related