# SQL IF EXIST in a script

**URL:** <https://forum.inductiveautomation.com/t/sql-if-exist-in-a-script/10781>\
**Category:** Ignition\
**Created:** [November 12, 2015, 6:53pm UTC](https://forum.inductiveautomation.com/t/sql-if-exist-in-a-script/10781 "2015-11-12T18:53:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\
**Post date:** [November 12, 2015, 6:53pm UTC](https://forum.inductiveautomation.com/t/sql-if-exist-in-a-script/10781/1 "2015-11-12T18:53:41Z")

</div>

I have a querry that will insert a new record into my DB listed below, but now I want to be able to updat the “setupsheet” if it already exist.

`queryValue1 = event.source.parent.getComponent('MoldName').selectedStringValue
queryValue2 = event.source.parent.getComponent('MachineName').selectedStringValue
queryValue3 = event.source.parent.getComponent('Text Area').text
if system.gui.confirm("Steve! Are you sure you want to Save changes?", 
	"Save Changes"):		
		system.db.runPrepUpdate("INSERT INTO setup_sheetlist (MoldName,machineName,Setupsheet) VALUES ('%s','%s','%s')" % (queryValue1, queryValue2 , queryValue3), getKey=1)	
		`

This is my query, its working in my DB but I’m a lost on how to implement it into the RunPrepUpdate,

```auto
IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='100SCX-3' AND Machinename='MM11')
	UPDATE setup_sheetlist 
	SET setupsheet ='updated value 3'
	WHERE moldname='100SCX-3' AND Machinename='MM11'

ELSE
       INSERT INTO setup_sheetlist(MoldName,machineName,Setupsheet)
       VALUES('100SCX-3','MM11','TRIAL 2')[/code]

This is what I have so far, this gi9ves me an error message 

[code]Error: java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='?' AND Machinename='?')UPDATE setup_sheetlist SET setupsheet ='updated value 3'	WHERE moldname='?' AND Machinename='?', [100SCX-3, MM09, 100SCX-3, MM09], , , false, false)

	caused by Exception: Error executing system.db.runPrepUpdate(IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='?' AND Machinename='?')UPDATE setup_sheetlist SET setupsheet ='updated value 3'	WHERE moldname='?' AND Machinename='?', [100SCX-3, MM09, 100SCX-3, MM09], , , false, false)
	caused by GatewayException: SQL error for "IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='?' AND Machinename='?')UPDATE setup_sheetlist SET setupsheet ='updated value 3'	WHERE moldname='?' AND Machinename='?'": The index 1 is out of range.
	caused by SQLServerException: The index 1 is out of range.
```

[code]queryValue1 = event.source.parent.getComponent(‘MoldName’).selectedStringValue  
queryValue2 = event.source.parent.getComponent(‘MachineName’).selectedStringValue  
queryValue3 = event.source.parent.getComponent(‘Text Area’).text  
if system.gui.confirm(“Steve! Are you sure you want to Save changes?”,  
“Save Changes”):

```
		system.db.runPrepUpdate("IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='?' AND Machinename='?')UPDATE setup_sheetlist SET setupsheet ='updated value 3'	WHERE moldname='?' AND Machinename='?'" ,[queryValue1,queryValue2,queryValue1,queryValue2] )
		ELSE 
		INSERT 
		INTO 
		setup_sheetlist (MoldName,machineName,Setupsheet)
		VALUES ('%s','%s','%s')" % (queryValue1, queryValue2 , queryValue3))

```

[/code]

---

<div class="post-metadata">

**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\
**Post date:** [November 12, 2015, 7:35pm UTC](https://forum.inductiveautomation.com/t/sql-if-exist-in-a-script/10781/2 "2015-11-12T19:35:39Z")

</div>

If you are using SQL Server, look into using merge.

MERGE INTO table\_name WITH (HOLDLOCK) USING table\_name ON (condition)  
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 …]  
WHEN NOT MATCHED THEN INSERT (column1 [, column2 …]) VALUES (value1 [, value2 …])

---

<div class="post-metadata">

**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\
**Post date:** [November 12, 2015, 9:45pm UTC](https://forum.inductiveautomation.com/t/sql-if-exist-in-a-script/10781/3 "2015-11-12T21:45:51Z")

</div>

I found my error, I was breaking the query into 3 parts, it’s only 1 query

[quote] system.db.runPrepUpdate(“IF EXISTS( SELECT \* FROM setup\_sheetlist WHERE moldname=? AND Machinename=?)UPDATE setup\_sheetlist SET setupsheet =? WHERE moldname=? AND Machinename=? ELSE INSERT INTO setup\_sheetlist (MoldName,machineName,Setupsheet) VALUES (?,?,?)” , (queryValue1,queryValue2,queryValue3,queryValue1,queryValue2,queryValue1, queryValue2 , queryValue3 ))  
[/quote]