SQL IF EXIST in a script

Ignition
1

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,

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]

2

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 …])

3

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]