update on duplicate key,

[sahar eb]

Hi,
I have this query:

String insertTableSQL = "INSERT INTO dev_corpus"
                  +"(corpusid,corpus_name,corpusdesc,corpusimageids,rocf1,rocf2,rocf3,rocc1,rocc2,rocc3,corpusactive,corpusrunfrequency,corpuslastrun,corpuslastsync,rocsettingid,corpusaffinity,corpusterms,corpusdomain) VALUES"
                  +"(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                  +"ON DUPLICATE KEY UPDATE "
                  +"corpusid = VALUE(?)";

which throws this exceprion:

ORA-00933: SQL command not properly ended

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

and here is my table:

  CREATE TABLE "SBOOKS"."DEV_CORPUS" 
   (	"CORPUSID" NUMBER(9,0) NOT NULL ENABLE, 
	"CORPUS_NAME" VARCHAR2(768 BYTE) NOT NULL ENABLE, 
	"CORPUSDESC" VARCHAR2(4000 BYTE), 
	"CORPUSIMAGEIDS" VARCHAR2(768 BYTE), 
	"ROCF1" FLOAT(63), 
	"ROCF2" FLOAT(63), 
	"ROCF3" FLOAT(63), 
	"ROCC1" FLOAT(63), 
	"ROCC2" FLOAT(63), 
	"ROCC3" FLOAT(63), 
	"CORPUSACTIVE" NUMBER(3,0), 
	"CORPUSRUNFREQUENCY" NUMBER(3,0), 
	"CORPUSLASTRUN" DATE, 
	"CORPUSLASTSYNC" DATE, 
	"ROCSETTINGID" NUMBER(3,0), 
	"CORPUSAFFINITY" NUMBER(3,0), 
	"CORPUSTERMS" VARCHAR2(4000 BYTE), 
	"CORPUSDOMAIN" NUMBER(3,0), 
	 PRIMARY KEY ("CORPUSID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DDATA"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DDATA" ;

can you tell me what is wrong I try to run it in MYSQL gives me the same error. do you know what part of my query is wrong? how can I solve this?

Thank you

[chris webster]

I don't use MySQL so the syntax for this kind of merge is unfamiliar, but what does the "VALUE(?)" do? Maybe try just using the place-holder "?" instead. Also, CORPUSID is your primary key, so you will only get to the "ON DUPLICATE" code if the Corpus ID value already exists. So why do you need to update it? you should not update primary keys anyway - one of the characteristics of a primary key is that it should not change.

[sahar eb]

Dear Chris,
I have used only ? instead of vale(?), no result, same error. yes, but this utility is actually a Backyp process and this snippet is for to restore section, ex.: in case that a row with a particular primary key has had some unwanted changed, or wrong ones, or have been deleted by mistake, we want to update If it exists. it will be updated to the last version of the backup or will be inserted.


Thanks!

[Paul Clapham]

I use MySQL but I haven't ever used the INSERT... ON DUPLICATE KEY UPDATE... feature. So I googled it: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html and I think you ought to look more carefully at what's supposed to be in the UPDATE clause. The examples in that manual page are kind of lame but I'm pretty sure you aren't using it right. Don't you have to specify the values for the fields you want to update in that clause?

[Martin Vajsar]

Your original question mentions Oracle. Oracle doesn't support this syntax (you could use the MERGE statement in Oracle instead).

So, even if you solve the problem for MySQL, you need to handle MySQL and Oracle differently for this functionality to work.

[sahar eb]

Thank you, yes, now I am using oracle sql developer comand window to test this:

MERGE INTO dev_corpus a  
  USING (SELECT corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain FROM dual WHERE corpusId = 1000156 AND corpus_name = 'sahar' AND corpusdesc = 'sahaaaaa' AND corpusimageids IS NULL AND rocf1 IS NULL AND rocf2 IS NULL AND rocf3 IS NULL AND rocc1 IS NULL AND rocc2 IS NULL AND rocc3 IS NULL AND corpusactive IS NULL AND corpusrunfrequency IS NULL AND corpuslastrun IS NULL AND corpuslastsync IS NULL AND rocsettingid IS NULL AND corpusaffinity IS NULL AND corpusterms IS NULL AND corpusdomain IS NULL) incoming 
  ON (a.corpusid = incoming.corpusid )  
  WHEN MATCHED THEN 
  UPDATE SET corpusid = incoming.corpusid , corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain 
  WHEN NOT MATCHED THEN 
  INSERT (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) 
  VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)
  

throws me this error:

Error at Command Line:2 Column:644
Error report:
SQL Error: ORA-00904: "CORPUSDOMAIN": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

here is crete table:

  CREATE TABLE "SBOOKS"."DEV_CORPUS" 
   (	"CORPUSID" NUMBER(9,0) NOT NULL ENABLE, 
	"CORPUS_NAME" VARCHAR2(768 BYTE) NOT NULL ENABLE, 
	"CORPUSDESC" VARCHAR2(4000 BYTE), 
	"CORPUSIMAGEIDS" VARCHAR2(768 BYTE), 
	"ROCF1" FLOAT(63), 
	"ROCF2" FLOAT(63), 
	"ROCF3" FLOAT(63), 
	"ROCC1" FLOAT(63), 
	"ROCC2" FLOAT(63), 
	"ROCC3" FLOAT(63), 
	"CORPUSACTIVE" NUMBER(3,0), 
	"CORPUSRUNFREQUENCY" NUMBER(3,0), 
	"CORPUSLASTRUN" DATE, 
	"CORPUSLASTSYNC" DATE, 
	"ROCSETTINGID" NUMBER(3,0), 
	"CORPUSAFFINITY" NUMBER(3,0), 
	"CORPUSTERMS" VARCHAR2(4000 BYTE), 
	"CORPUSDOMAIN" NUMBER(3,0), 
	 PRIMARY KEY ("CORPUSID")

I spent almost 3 days, no progress, please if any idea.. how can I solve this ?

Thank you sooo much!

[Martin Vajsar]

The problem is that the DUAL table doesn't have column CORPUSDOMAIN (nor any other column you're selecting from it).

Instead ofselect corpusid, corpus_name from dual where corpusId = 1000156 AND corpus_name = 'sahar' useselect 1000156 as corpusId, 'sahar' as corpus_name from dual (and similarly for all other columns you have in your select query). This is the way to obtain one row containing columns with given values in Oracle.

In your Java application, you should use parameters instead of hard-coded values, of course.

[sahar eb]

thanks, now I have this that works fine in oracle command window:

MERGE INTO dev_corpus a
  USING (SELECT 1000156 corpusid, 'sss2' corpus_name, 'sahaaaaaar' corpusdesc, null corpusimageids, null rocf1, null rocf2, null rocf3, null rocc1, null rocc2, null rocc3, null corpusactive, null corpusrunfrequency, null corpuslastrun, null corpuslastsync, null rocsettingid, null corpusaffinity, null corpusterms, null corpusdomain FROM dual) incoming 
  ON (a.corpusid = incoming.corpusid )  
  WHEN MATCHED THEN 
  UPDATE SET corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain 
  WHEN NOT MATCHED THEN 
  INSERT (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) 
  VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)

but when I put in java class , it works for insert but it does not work in case it is an update, it just freezes, until I delete the row from DB then it inserts it. So no update!!!
here is the java code:

  preparedStatement = dbConnection.prepareStatement("MERGE INTO dev_corpus a " +
                              "USING (SELECT ? corpusid, ? corpus_name, ? corpusdesc, ? corpusimageids, ? rocf1, ? rocf2, ? rocf3, ? rocc1, ? rocc2, ? rocc3, ? corpusactive, ? corpusrunfrequency, ? corpuslastrun, ? corpuslastsync, ? rocsettingid, ? corpusaffinity, ? corpusterms, ? corpusdomain FROM dual) incoming " +
                              "ON (a.corpusid = incoming.corpusid ) " +
                              "WHEN MATCHED THEN " +
                              "UPDATE SET corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain " +
                              "WHEN NOT MATCHED THEN " +
                              "INSERT (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) " +
                              "VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)");
.... seting parameters
preparedStatement.executeUpdate();

do you know what I am doing wrong here?

Thank you so much!!!

[sahar eb]

p.s. although I have e.printstacktrace it does not throw any error just stuck in pstmt.execute().