From derby-user-return-15584-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri May 16 23:12:15 2014 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 4472211A88 for ; Fri, 16 May 2014 23:12:15 +0000 (UTC) Received: (qmail 89523 invoked by uid 500); 16 May 2014 11:49:35 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 72272 invoked by uid 500); 16 May 2014 11:37:13 -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 19051 invoked by uid 99); 16 May 2014 11:24:54 -0000 Received: from Unknown (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 16 May 2014 11:24:54 +0000 X-ASF-Spam-Status: No, hits=-0.7 required=5.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of john.foreign@gmail.com designates 74.125.82.177 as permitted sender) Received: from [74.125.82.177] (HELO mail-we0-f177.google.com) (74.125.82.177) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 15 May 2014 09:10:56 +0000 Received: by mail-we0-f177.google.com with SMTP id x48so750424wes.36 for ; Thu, 15 May 2014 02:10:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; bh=rCBvy1rUJKMxkg1SpG5pjD1nyd2oe2emhRMHi6l028o=; b=Pw1vIAgIlcfZQHY0sjbaqc+WZcVb+m6A+vWVFZrLbuYTEWyogRynWyhzC5XxtJidSa d9Q448qtS3p/CPTlwAX/bk1XZTkcmiB89Xc7D4/57FtQ5UreguhkV558e8zIQaE+sjpf +1Z1bo8eIBx2jdT68Czg+PqHmF5jsVplcjEPM8dgoG4yJmuB8UVjZ7bObxiPlpdU4Tsb e+O/tPRhYy/MjIxONLlULjocwrykyMZgxiMdX1AiavjAV87K2Uh+g7xMePTBWGESlNXJ k3GNavioDt1ySf2NXtuV7cHedCluosM1+5pi1Foqq2FcVTbx3g4YGYdX73IoKGVXnKt7 Cf8w== X-Received: by 10.194.89.168 with SMTP id bp8mr618716wjb.73.1400145033809; Thu, 15 May 2014 02:10:33 -0700 (PDT) Received: from [192.168.1.101] (bzq-84-111-131-91.red.bezeqint.net. [84.111.131.91]) by mx.google.com with ESMTPSA id xm20sm8725597wib.19.2014.05.15.02.10.31 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 15 May 2014 02:10:32 -0700 (PDT) Message-ID: <5374847F.30908@gmail.com> Date: Thu, 15 May 2014 12:10:23 +0300 From: John English User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.5.0 MIME-Version: 1.0 To: Derby Discussion Subject: Re: Unique constraints and nulls References: <53737504.6000902@gmail.com> <53737F1B.6030403@gmail.com> In-Reply-To: <53737F1B.6030403@gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org On 14/05/2014 17:35, John English wrote: > delete from Foo where A=? and B=? > > However, when the value for B is null, nothing gets deleted (presumably due to > the query containing B=NULL rather than B IS NULL). I now have this, which works (but is rather ugly)... I'd be grateful if anyone can tell me a more elegant way to do it: try (Transaction t = db.startTransaction()) { if (b == null) { db.update("delete from Foo where A=? and B is null", a); } else { db.update("delete from Foo where A=? and B=?", a, b); } update("insert into Foo values(?,?)", a, b); t.commit(); } -- John English