SQL INSERT INTO... WHERE.... Statement

Ignition
1

This below statement works great.

system.db.runPrepUpdate(“INSERT INTO QC_CupMeasurements_Temp (testValue) VALUES (?)”, [testValue],“Ignition”)

But I want to add “WHERE sampleID = somevariable”

Whats the syntax? I know I am going to facepalm when I see it.

2

Try this:

INSERT INTO QC_CupMeasurements_Temp (testValue)
SELECT something FROM sometable
WHERE sampleID = somevariable
3

adamaustin, I get the SQL syntax, I was referring to the syntax as it relates to putting the SQL statement within the system.db.runPrepUpdate statement.

4

Specifically here is my issue:

sampleID1 = 02-20140522100349 t1Leak1 = 25 system.db.runPrepUpdate("INSERT INTO QC_CupMeasurements_Temp (t1Leak) VALUES (?) SELECT sampleID,t1Leak FROM QC_CupMeasurements_Temp WHERE (sampleID) = (?)", (t1Leak1),(sampleID1),"Ignition")

Result is an error:

[code]Traceback (most recent call last):
File “”, line 3, in
java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value ‘25’ into type: class [Ljava.lang.Object;

caused by ClassCastException: Cannot coerce value '25' into type: class [Ljava.lang.Object;

Ignition v7.6.4 (b2013112117)
Java: Oracle Corporation 1.7.0_51
[/code]

5

Try:

system.db.runPrepUpdate("""INSERT INTO QC_CupMeasurements_Temp (testValue) 
SELECT something FROM sometable 
WHERE sampleID = ?""", [somevariable],"Ignition")
6

Try:

system.db.runPrepUpdate("INSERT INTO QC_CupMeasurements_Temp (SAMPLEID, t1Leak) SELECT sampleID, ? FROM QC_CupMeasurements_Temp WHERE (sampleID) = (?)", [t1Leak1,sampleID1],"Ignition")
7

I wanted to use UPDATE not INSERT…

DOH!!