Update Primary Key Using Primary Key

[Ralf Assmann <ra...@innovations.de>]

Hi there,

we are using a database table with three columns column1, column2, 
column3. The primary key are column1 and column2.

Now, we want to do an update using iBatis, the column2 should be 
updated. The update itself should be done by using the primary key as 
the where part. For this, we created (at least we wanted to do so) the 
following sql statement:

update table set column2 = #?# where column1 = #column1# and column2 = 
#column2#

The parameters are part of a parameter class, using the getter of this 
class. The problem now is, that the update should be done by the 
attributes of the primary key, but the primary key itself should be 
modified. That is the parameter #?# in the statement above.

How is it possible to pass the parameter for the update using the 
parameter class? Writing #column2# instead of #?#,  the set part will 
use the same value as the value used in the where part (so the update 
does not make any sense).  Is there any way in doing so using a 
parameter class or can't this be done by a parameter class and a map has 
to be used (including keys like 'column2new' and 'column2old')?

Thanks for your answers.

Best regards,

Ralf.

Re: Update Primary Key Using Primary Key

[Nils Winkler <ni...@nilswinkler.com>]

In addition to the suggestion already made, IMHO it's generally a bad
idea to change a primary key's value. This will usually cause havoc if
you use the primary key as a foreign key in other tables - now you have
to update those as well.

Because of this, I always use a unique technical key, preferably just
one column. Once this value has been assigned, it can not be changed -
ever.

Nils

On Wed, 18 Oct 2006 09:35:00 +0200, "Ralf Assmann"
<ra...@innovations.de> said:
> 
> Hi there,
> 
> we are using a database table with three columns column1, column2, 
> column3. The primary key are column1 and column2.
> 
> Now, we want to do an update using iBatis, the column2 should be 
> updated. The update itself should be done by using the primary key as 
> the where part. For this, we created (at least we wanted to do so) the 
> following sql statement:
> 
> update table set column2 = #?# where column1 = #column1# and column2 = 
> #column2#
> 
> The parameters are part of a parameter class, using the getter of this 
> class. The problem now is, that the update should be done by the 
> attributes of the primary key, but the primary key itself should be 
> modified. That is the parameter #?# in the statement above.
> 
> How is it possible to pass the parameter for the update using the 
> parameter class? Writing #column2# instead of #?#,  the set part will 
> use the same value as the value used in the where part (so the update 
> does not make any sense).  Is there any way in doing so using a 
> parameter class or can't this be done by a parameter class and a map has 
> to be used (including keys like 'column2new' and 'column2old')?
> 
> Thanks for your answers.
> 
> Best regards,
> 
> Ralf.
> 
> 
--
==================================
nils@nilswinkler.com

RE: Update Primary Key Using Primary Key

[Poitras Christian <Ch...@ircm.qc.ca>]

Both of your suggestions are ok.

Personnally, I would use a Map. Unless you already have a parameter
class.
For the Map, just add your object and old column2 value in the Map and
use this.

update table set column2 = #object.column2# where column1 =
#object.column1# and column2 = #oldColumn2#

Christian

-----Original Message-----
From: Ralf Assmann [mailto:ralf.assmann@innovations.de] 
Sent: Wednesday, 18 October 2006 03:35
To: user-java@ibatis.apache.org
Subject: Update Primary Key Using Primary Key


Hi there,

we are using a database table with three columns column1, column2,
column3. The primary key are column1 and column2.

Now, we want to do an update using iBatis, the column2 should be
updated. The update itself should be done by using the primary key as
the where part. For this, we created (at least we wanted to do so) the
following sql statement:

update table set column2 = #?# where column1 = #column1# and column2 =
#column2#

The parameters are part of a parameter class, using the getter of this
class. The problem now is, that the update should be done by the
attributes of the primary key, but the primary key itself should be
modified. That is the parameter #?# in the statement above.

How is it possible to pass the parameter for the update using the
parameter class? Writing #column2# instead of #?#,  the set part will
use the same value as the value used in the where part (so the update
does not make any sense).  Is there any way in doing so using a
parameter class or can't this be done by a parameter class and a map has
to be used (including keys like 'column2new' and 'column2old')?

Thanks for your answers.

Best regards,

Ralf.