Best way to check whether a row has been updated in SQL

Question

I am learning NodeJS and Mongo Db and I got stuck. Could you please help me If you know solution?

I have 2 questions

1.)I have 2 collections in mongo db I tried to find out which projects user has. For example michaela has projects :nodejs, test2 jasmin has projects: test2 (I don't want to know who are coders for current project but I want to know what are projects for current user)

data what I want to get should look something like this

[{
  id: "some value"
  username: "michaela"
  email:"some value"
  password:"some value"
  projects:"[{
     project_name:"node js"
     project_description:"learn node js"
     deadline:"2021-11-12"
     status:"todo" 
  },
  {
     project_name:"test2"
     project_description:"learn node js"
     deadline:"2021-11-12"
     status:"todo" 
  }]
  },
  id: "some value"
  username: jasmin"
  email:"some value"
  password:"some value"
  projects:"[
  {
     project_name:"test2"
     project_description:"learn node js"
     deadline:"2021-11-12"
     status:"todo" 
  }]

}]

I don't understand exactly how look up works. I tried following example(test) but it doesn't work

I also don't understand how to connect 2 db for example allMembers and all projects I merged into one array but I don't think this is correct way how to do it.

this is my model

const usersCollection = require('../db').db().collection("users")
const projectsCollection = require('../db').db().collection("projects")

Team.prototype.getAllMembers = function(){
    return new Promise (async (resolve, reject) => {
        try{
            let allMembers = await usersCollection.find({}).toArray()
            let allprojects = await projectsCollection.find({}).toArray()
                        let test = await  usersCollection.aggregate([
                {$lookup: {from: "projects", localField: "username", foreignField: "coder", as: "projects"}},
                {$project: {
                    username:1, 
                    projects:{
                         name:{$arrayElemAt: ["$projects.project_name", 0]},
                         description:{$arrayElemAt: ["$projects.project_description", 0]},
                    },
                    email:1,
                    password:1,
                  }}
              ]).toArray()
              console.log("test", test)
            let arr3 = [...allMembers, ...allprojects]
            resolve(arr3)
        }catch{
            reject()
        }
    })
}

this is my controller

exports.home = function(req, res) {
    //when user is log in
    if (req.session.user) {
     let teamMember = new Team(req.body)
      teamMember.getAllMembers().then((data)=>{
        console.log("members data", data)
          if(data){
            res.render('members', {data: data })
          }else{
            res.send("on this page are no data")
          }
      }).catch((err)=>{
          res.send("problem")
      }) 
      //when user is not log in, flash tie errory z db automaticky vymaze po pouziti
    } else {
      res.render('login', {errors: req.flash('errors'), regErrors: req.flash('regErrors')})
    }
  }

and this is how it looks in db

Thank you in advance

Solution 1

Check the old vs. new data in your code instead of doing it in a query.

No need to bother the DB layer unnecessarily if data didn't change at all.

In short, if data didn't change, don't send the UPDATE statement.

Solution 2

One way is to start a transaction, select the contents of the row and compare it to what you're going to update it to. If they don't match, then do the update and end the transaction. If they match, rollback the transaction.

Solution 3

Sounds like you are going through a table and modifying some rows, then you want to go BACK through the table a second time and update the timestamp for the rows that were just changed.

Don't do it in two passes. Just update the date/time at the same time as you update whatever other columns you are changing:

UPDATE myTable
SET retailprice = wholesaleprice * 1.10,
    lastmodified = GetDate()
WHERE ...

Or are you issuing an update statement on ALL rows, but for most rows, it just sets it to the value it already has? Don't do that. Exclude those rows that wouldn't be modified in your where clause:

UPDATE myTable
SET retailprice = wholesaleprice * 1.10,
    lastmodified = GetDate()
WHERE retailprice <> wholesaleprice * 1.10

Solution 4

If you are using sql 2005/2008 then you can do as follows in the stored procedure.

update  newTable 
set     readKey='1'
output  inserted.id,
        inserted.readKey as readKey,
        deleted.readKey as prevReadKey
into @tempTable
where id = '1111'

Then you can select from @tempTable to verify if the prevReadKey and readKey has similar value if both has similar value you can reset your last modified datetime.

This way you don't have to fire multiple queries on the table in the case when a value is actually changing. But yes in the case when the value is not changing, this will be firing two update statements where none is required. This should be OK if those cases are rare.

P.S. NOTE:- The query given might be syntactically wrong as it is not tested. But this is the way your problem can be solved. I have done it in following way using OUTPUT clause with Merge statement in one of my project and it can be done with update statement too. Here is the reference of OUTPUT Clause

Solution 5

That's when a DAL is handy. It keeps track of all colums so if none changed then I don't even send an UPDATE statement to the database.

View more solutions

Comments

[user3529201]

while this is true, there are situations where you could be forced to do a previous query (SELECT) to get the data, and a 2nd one to do the actual update - in these cases i would probably just do a single UPDATE query, even if no changes occur.

[James Piggot]

How do you know this is a SQL Server set-up, because of history or previous questions?

[IsmailS]

I agree with @jcinacio but the OPs question still stays if we go with @jcinacio's way. So, if OP is using sql 2005/2008 then OUTPUT clause can be used to compare the new and old values. See my answer below.

[Cody Gray]

Comments are not for extended discussion or debugging sessions; this conversation has been moved to chat. Please be sure to edit all important information into the answer itself!