Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 54565 invoked from network); 2 Apr 2008 09:50:10 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 2 Apr 2008 09:50:10 -0000 Received: (qmail 79317 invoked by uid 500); 2 Apr 2008 09:50:09 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 79294 invoked by uid 500); 2 Apr 2008 09:50:09 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 79283 invoked by uid 99); 2 Apr 2008 09:50:09 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2008 02:50:09 -0700 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [192.18.6.24] (HELO gmp-eb-inf-2.sun.com) (192.18.6.24) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Apr 2008 09:49:17 +0000 Received: from fe-emea-09.sun.com (gmp-eb-lb-2-fe2.eu.sun.com [192.18.6.11]) by gmp-eb-inf-2.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m329nZRe009898 for ; Wed, 2 Apr 2008 09:49:35 GMT Received: from conversion-daemon.fe-emea-09.sun.com by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <0JYO00101Z1RGH00@fe-emea-09.sun.com> (original mail from Knut.Hatlen@Sun.COM) for derby-user@db.apache.org; Wed, 02 Apr 2008 10:49:35 +0100 (BST) Received: from localhost ([129.159.112.134]) by fe-emea-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <0JYO004EEZAKVZ20@fe-emea-09.sun.com> for derby-user@db.apache.org; Wed, 02 Apr 2008 10:49:33 +0100 (BST) Date: Wed, 02 Apr 2008 11:49:32 +0200 From: Knut Anders Hatlen Subject: Re: Update Substring of a field In-reply-to: <20080402081436.GE4586@khepri35.norway.sun.com> Sender: Knut.Hatlen@Sun.COM To: Derby Discussion Message-id: Organization: Sun Microsystems MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT References: <20080402081436.GE4586@khepri35.norway.sun.com> User-Agent: Gnus/5.110007 (No Gnus v0.7) Emacs/22.1 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org "Bernt M. Johnsen" 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