• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

update on duplicate key,

 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
p.s. although I have e.printstacktrace it does not throw any error just stuck in pstmt.execute().
 
A day job? In an office? My worst nightmare! Comfort me tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic