SQL query compare and replace

Ignition
1

I have a submit button that is dumping a selection of tag values from our SQDCME screen based on selections made by our coordinators. the problem we are having is…if a coordinator puts in his information for his department and then forgets he entered it for the day, he goes and enters it again, which causes 2 rows of data to exist within the table in the database. So when we go to run a report from that data, both those rows are being calculated in with the daily numbers we are looking for. Is there a way to write the query so that it looks to see if there already is a row for that day and department and overwrites the information there if the coordinator double enters it? Here is the script for what the sumbit button currently does:

if system.gui.confirm(“Have you selected and entered all the appropriate information?”, “Are you sure?”, 0):
AreaSelected = event.source.parent.getComponent(‘AreaSelected’).text
ShiftSelected = event.source.parent.getComponent(‘ShiftSelected’).text
Stextfield = event.source.parent.getComponent(‘Stextfield’).text
Qtextfield = event.source.parent.getComponent(‘Qtextfield’).text
Dtextfield = event.source.parent.getComponent(‘Dtextfield’).text
Ctextfield = event.source.parent.getComponent(‘Ctextfield’).text
Mtextfield = event.source.parent.getComponent(‘Mtextfield’).text
Etextfield = event.source.parent.getComponent(‘Etextfield’).text
ProductionDate = event.source.parent.getComponent(‘ProductionDate’).text
Loggedin = event.source.parent.getComponent(‘LoginLabel’).text

query = "INSERT INTO SQDCME.dbo.SQDCME (t_stamp, Shift, Area, Safety, Quality, Delivery, Cost, Moral, Environment, Coordinator) VALUES (?,?,?,?,?,?,?,?,?,?)"

args = [ProductionDate, ShiftSelected, AreaSelected, Stextfield, Qtextfield, Dtextfield, Ctextfield, Mtextfield, Etextfield, Loggedin]

system.db.runPrepUpdate(query,args)

else:
system.gui.messageBox(“User canceled the update.”, “Update Canceled”)

tags = ["[Client]SQDCME/Stacker_1", “[Client]SQDCME/Stacker_2”, “[Client]SQDCME/GreenLine”, “[Client]SQDCME/BlueLine”, “[Client]SQDCME/RoughMill”, “[Client]SQDCME/Janitorial”, “[Client]SQDCME/LumberDrying”, “[Client]SQDCME/ForkLifts”, “[Client]SQDCME/Maintenance”, “[Client]SQDCME/PX”, “[Client]SQDCME/Finish”, “[Client]SQDCME/Boilers”, “[Client]SQDCME/Autodefecting”, “[Client]SQDCME/Offline”, “[Client]SQDCME/Training”, “[Client]SQDCME/AreaSelected”, “[Client]SQDCME/ShiftSelected”, “[Client]SQDCME/Shift_1”, “[Client]SQDCME/Shift_2”, “[Client]SQDCME/Shift_3”]
values = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
system.tag.writeAll(tags,values)

event.source.parent.getComponent(‘Stextfield’).intValue = 0
event.source.parent.getComponent(‘Qtextfield’).intValue = 0
event.source.parent.getComponent(‘Dtextfield’).intValue = 0
event.source.parent.getComponent(‘Ctextfield’).intValue = 0
event.source.parent.getComponent(‘Mtextfield’).intValue = 0
event.source.parent.getComponent(‘Etextfield’).intValue = 0
event.source.parent.getComponent(‘Dtextfield’).visible = 1
event.source.parent.getComponent(‘Container’).getComponent(‘Dtank’).visible = 1
event.source.parent.getComponent(‘Dgoaltextfield’).visible = 1

2

Do you want to replace the first row entered or stop the second row from entering? If you want to over write the first entry look into using Replace Into instead of Insert Into. If you want to stop a second row from being entered look at using a separate query to check for duplicates or use If not exists in your query. You should be able to look up the syntax with google for your db flavor.

3

If I use the Replace Into command, would it still add a row if one does not already exist?

4

Yes it will as long as your DB supports the Replace Into syntax

5

In PostgreSQL, you use the INSERT .... ON CONFLICT .... syntax with a primary key or unique index constraint. MySQL & MariaDB have an UPSERT statement.

1 Like
6

Maybe I am not looking at this correctly but how does it know which row to replace? Where do you set which row to look at? I don’t want to replace/remove older rows that have data in them.

7

I believe that it looks for duplicate primary keys before replacing.

replace into myTable (primaryKey, col1,col2,col3)
               values(1234,"col1Data","col2Data","col3Data")
8

I’m assuming ProductionDate , AreaSelected and ShiftSelected. are not unique values in their columns. In that instance, you can run a scalar query to see if the row already exists.

scalarQuery = "SELECT COUNT(*) FROM SQDCME.dbo.SQDCME WHERE t_stamp = ? AND Shift = ? AND Area = ?"
scalarArgs = [ProductionDate, ShiftSelected, AreaSelected]
rowExists = system.db.runScalarPrepQuery(scalarQuery, scalarArgs) > 0
if rowExists:
    # Update the Row
else:
    # Insert the row
9

Racy. Best to create a unique index across those columns and use the DB's bult-in conflict resolution.

2 Likes
10

They are not unique values. We have coordinators that enter daily information. so the t_stamp is the same for values entered the same day but there should only be one row for each individual department that is entered per day.

11

As Phil suggests (and I forgot about), having a multi-column index for t_stamp, Area, and Shift should make it unique enough to work.

12

@toddayers, Are you using SQL Server? If so, you would need to use MERGE. Sample statement here:

13

Ok… so in this instance the values will equal the memory tag locations I have in the project right, or in this case it is a combination of tag values and text field values?

14

You may want to look for a MERGE in your database DML.
I use MERGE to UPDATE existing records or INSERT missing records but it all happens on the database with a single exchange vs. executing query, evaluate the results then execute the next statement (UPDATE or INSERT).

15

@JordanCClark, Just now getting a chance to circle back around to this issue: This is my current script along with my query-

if system.gui.confirm(“Have you selected and entered all the appropriate information?”, “Are you sure?”, 0):
AreaSelected = event.source.parent.getComponent(‘AreaSelected’).text
ShiftSelected = event.source.parent.getComponent(‘ShiftSelected’).text
Stextfield = event.source.parent.getComponent(‘Stextfield’).text
Qtextfield = event.source.parent.getComponent(‘Qtextfield’).text
Dtextfield = event.source.parent.getComponent(‘Dtextfield’).text
Ctextfield = event.source.parent.getComponent(‘Ctextfield’).text
Mtextfield = event.source.parent.getComponent(‘Mtextfield’).text
Etextfield = event.source.parent.getComponent(‘Etextfield’).text
ProductionDate = event.source.parent.getComponent(‘ProductionDate’).text
Loggedin = event.source.parent.getComponent(‘LoginLabel’).text

query = "INSERT INTO SQDCME.dbo.SQDCME (t_stamp, Shift, Area, Safety, Quality, Delivery, Cost, Moral, Environment, Coordinator) VALUES (?,?,?,?,?,?,?,?,?,?)"

args = [ProductionDate, ShiftSelected, AreaSelected, Stextfield, Qtextfield, Dtextfield, Ctextfield, Mtextfield, Etextfield, Loggedin]

system.db.runPrepUpdate(query,args)

else:
system.gui.messageBox(“User canceled the update.”, “Update Canceled”)

So are you saying replace the Insert into statement with a merge? And what is the condition you mention?

16

If you are

  • using SQL Server
  • performing and upsert (update if it already exists, insert if it does not)

then yes, you will need MERGE. The condition is what decides if there is a matching row.

I've not personally tried using runPrepUpdate() with a MERGE, but it would look something like this.

Note that we now have twice as many arguments, but fortunately, they are in the same order. We can multiply the list by 2, and easily get all the arguments we need.

query = """MERGE INTO SQDCME.dbo.SQDCME WITH (HOLDLOCK) USING SQDCME.dbo.SQDCME ON (t_stamp = ? AND Shift = ? AND Area = ?)
           WHEN MATCHED THEN UPDATE SET Safety = ?, Quality = ?, Delivery = ?, Cost = ?, Moral = ?, Environment = ?, Coordinator = ?
           WHEN NOT MATCHED THEN INSERT (t_stamp, Shift, Area, Safety, Quality, Delivery, Cost, Moral, Environment, Coordinator) VALUES (?,?,?,?,?,?,?,?,?,?)"""

# Note the *2 at the end of the line, because we need to specify the arguments twice.
# (e.g. [1, 2, 3] * 2 results in [1, 2, 3, 1, 2, 3])
args = [ProductionDate, ShiftSelected, AreaSelected, Stextfield, Qtextfield, Dtextfield, Ctextfield, Mtextfield, Etextfield, Loggedin] * 2

system.db.runPrepUpdate(query,args)
17

If you put the MERGE in a Named Query, then you don’t need to double the parameters; just use the same parameters in two different parts of the MERGE statement.