Re: Update Substring of a field

["Bernt M. Johnsen" <Be...@Sun.COM>]

>>>>>>>>>>>> Reginald Johnson wrote (2008-03-30 00:48:33):
> Is it possible to update the substring of a field?  I'm trying to do
> something like this:
> 
> UPDATE tblIntervals SET SessionMouseID = 'ggg',  substr(SessionID, 0, 3) =
> 'ggg' WHERE SessionMouseID = 'bbb'

Neither Derby, nor the SQL standard supports a substring expression on
the left side of the assignment.

Your options is to eiher do it in java through JDBC, or modify your
schema to store SessionMouseID and e.g. SessionIDRest and do a
concatenation each time you need a SessionID. Don't know if the latter
is feasible in your case, though.

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway

Re: Update Substring of a field

[Knut Anders Hatlen <Kn...@Sun.COM>]

"Bernt M. Johnsen" <Be...@Sun.COM> writes:

>>>>>>>>>>>>> Reginald Johnson wrote (2008-03-30 00:48:33):
>> Is it possible to update the substring of a field?  I'm trying to do
>> something like this:
>> 
>> UPDATE tblIntervals SET SessionMouseID = 'ggg',  substr(SessionID, 0, 3) =
>> 'ggg' WHERE SessionMouseID = 'bbb'
>
> Neither Derby, nor the SQL standard supports a substring expression on
> the left side of the assignment.
>
> Your options is to eiher do it in java through JDBC, or modify your
> schema to store SessionMouseID and e.g. SessionIDRest and do a
> concatenation each time you need a SessionID. Don't know if the latter
> is feasible in your case, though.

You can also keep the schema as it is and use a combination of SUBSTR
and concatenation in the UPDATE statement.

UPDATE tblIntervals
  SET SessionMouseID = 'ggg',
      SessionID = 'ggg' || SUBSTR(4, LENGTH(SessionId) - 3)
  WHERE SessionMouseId = 'bbb'

-- 
Knut Anders