Update row in database based on column

[CLH239]

Hi, 

I am trying to figure out a way to update a row that's in a SQL database based on two columns. If the Sales order and the Sensor number match the Sales order and sensor number that already exists in the database, I want it to update the Sensor reading column. If neither already exists then I want it to create a new row. How would I go about doing this?

Thanks,

Chris

[tst]

First of all, the function you're calling is insert, not update, so that certainly won't work.

Second, some DBs have an insert or update statement. The syntax is different for each one, so you would have to search for whether the DB you use has such a command and what its syntax is. You can then use the Format Into String primitive to construct the command and use the execute query VI to run it.

If your DB doesn't have this command, I believe the common recommendation for this is to do this yourself. Usually this would be done in a stored procedure, but you can also do in LV - start a transaction, then do an update. If the update failed, do an insert, then end the transaction. I think (not sure) that the DB toolkit might have a VI for constructing the update statement similar to the insert VI (it might not be on the palette). If there is no such VI, then you can build the statement yourself using Format Into String.

[ben64 · Accepted solution]

You can use the DB tools Execute Query.vi. Here is an example for an MS Access 2010 database where one column value is updated in all the rows satisfying the WHERE conditions.

Ben64

[CLH239]

Thanks Ben and Tst,

Your answers helped and it works great.

Chris