• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

update on duplicate key,

 
sahar eb
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have this query:


which throws this exceprion:


and here is my table:


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
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, yes, now I am using oracle sql developer comand window to test this:


throws me this error:



here is crete table:


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

Thank you sooo much!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The problem is that the DUAL table doesn't have column CORPUSDOMAIN (nor any other column you're selecting from it).

Instead ofuse(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
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks, now I have this that works fine in oracle command window:



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:


do you know what I am doing wrong here?

Thank you so much!!!
 
sahar eb
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
p.s. although I have e.printstacktrace it does not throw any error just stuck in pstmt.execute().
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic