X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id EF94653E67
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  9 May 2005 14:30:58 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 58665-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  9 May 2005 17:30:54 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.195])
	by svr1.postgresql.org (Postfix) with ESMTP id B023453DC4
	for <pgsql-sql@postgresql.org>; Mon,  9 May 2005 14:30:53 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id 68so2141316wra
	for <pgsql-sql@postgresql.org>; Mon, 09 May 2005 10:30:54 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=HY2FH8W5bFTbd7O92gywA0pwhYM7iQ2O+wj6JJjOUDEEFyKaxLBrU1BVqTUidl1unx4HV/BJtsaNq0eOD5KQrmUYmvqNR//GttC5X/46W3j46aqRNEpfAt97hjirxsGP0fyRmnObSGeyr5dt8M4dfd5aGDNorNLsCWZxUTnk2Co=
Received: by 10.54.34.28 with SMTP id h28mr2675281wrh;
	Mon, 09 May 2005 10:30:53 -0700 (PDT)
Received: by 10.54.65.12 with HTTP; Mon, 9 May 2005 10:30:53 -0700 (PDT)
Message-ID: <6d8daee30505091030f5c7828@mail.gmail.com>
Date: Mon, 9 May 2005 10:30:53 -0700
From: Tony Wasson <ajwasson@gmail.com>
Reply-To: Tony Wasson <ajwasson@gmail.com>
To: Mark Fenbers <Mark.Fenbers@noaa.gov>
Subject: Re: default value for select?
Cc: pgsql-sql@postgresql.org
In-Reply-To: <427F9685.8050104@noaa.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <427F9685.8050104@noaa.gov>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.273 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_ABUSE, RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200505/90
X-Sequence-Number: 21403

On 5/9/05, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:
>  I want to update a column in myTable.  The value this column is set to
> depends on a nested select statement which sometimes returns 0 rows inste=
ad
> of 1.  This is a problem since the column I'm trying to update is set to
> refuse nulls.  Here's a sample:
> =20
>  update myTable set myColumn =3D (Select altColumn from altTable where
> altColumn !=3D 'XXX' limit 1) where myColumn =3D 'XXX';
> =20
>  MyColumn cannot accept nulls, but sometimes "Select altColumn ..." retur=
ns
> 0 rows, and thus, the query fails. =20
> =20
>  Is there a way to set a default value to be inserted into myColumn if an=
d
> when "select altColumn ..." returns zero rows?
> =20
>  Mark

Mark,=20
You can work around this by using a CASE statement. In this case, test
for a NULL from your subquery. This is not elegant at all, but it
should do what you are wanting.

update myTable set myColumn =3D (CASE
  WHEN (Select altColumn from altTable where  altColumn !=3D 'XXX'
limit 1) IS NULL
    THEN 'some default value'
  ELSE (Select altColumn from altTable where  altColumn !=3D 'XXX' limit 1)
  END)
where myColumn =3D 'XXX';

Hope this helps...
Tony