From derby-user-return-15580-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri May 16 19:25:39 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 8E07B11D83 for ; Fri, 16 May 2014 19:25:39 +0000 (UTC) Received: (qmail 67075 invoked by uid 500); 16 May 2014 11:32:32 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 99119 invoked by uid 500); 16 May 2014 11:22:38 -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 35514 invoked by uid 99); 16 May 2014 11:09:59 -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:09:59 +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 209.85.212.174 as permitted sender) Received: from [209.85.212.174] (HELO mail-wi0-f174.google.com) (209.85.212.174) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 15 May 2014 09:11:08 +0000 Received: by mail-wi0-f174.google.com with SMTP id r20so9415182wiv.13 for ; Thu, 15 May 2014 02:10:45 -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=kIkb5hkw2Rb87ZVWALDvzwfZEvh/9Ac96jYXK0WVscPUCUP2eJ0KjxyJ5lBZgpOaWT ttiUb72ETM95azOvBey71pMN3lt6J4hKtcsRd4aOqN+boQ0JmQ7IeJeO5uPwHHpf2Xwp kC4CJuXiGy+UCFR1so2PnA5Ifenz64+bBwuZvicnr99qdYN4fWQ8qNSo6cz6mkq8aqrz aD9l8Y75CinxlPcm++KsK8i7H2s3CksYbqBMGW7J7QlwT+MZNe3fDExrNrPJL3eC68Xk IvP1CYOk7oVaTo0teF/jztF6svhnKcRmg7fybzdlVAqoleSrPAbxDZeBT++SyQw7jifx kTkw== X-Received: by 10.194.203.2 with SMTP id km2mr775678wjc.72.1400145045356; Thu, 15 May 2014 02:10:45 -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 d6sm8753090wiz.4.2014.05.15.02.10.43 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 15 May 2014 02:10:44 -0700 (PDT) Message-ID: <5374848B.30405@gmail.com> Date: Thu, 15 May 2014 12:10:35 +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