Re: Oracle SQL problem giving me a headache!

From: Alex Filonov <afilonov_at_pro-ns.net>
Date: Tue, 06 Feb 2001 15:54:56 GMT
Message-ID: <95p6ob$8et$1@nnrp1.deja.com>

In article <95n1pa$hv5v1$1_at_ID-68406.news.dfncis.de>,   "James Alexander Starritt" <james_at_jamesstarritt.com> wrote:
> As many of you will no doubt of notice from my past posts I am fairly
 new to
> Oracle. That aside and with the help of this group I have asked less
 and
> less and am actually using Oracle productivly ... so thanks ...
 praises
> aside I have enocunted a SQL script problem that is giving me a
 headache. I
> am sure that it is a product of my join but I need an expert eye to
 tell me
> whats going on and what to do next ...
>
> I have two tables, I have only included the relevent fields.
>
> location new_location(text), lockey (foreign key)
>
> locations location(text), lockey (primarykey)
>
> The location.new_location should validate against locations to make
 sure
> that the new_location is a proper entry. This works fine in the entry
 exists
> in locations but if it does not it changes new_location and lockey in
> location to NULL, NULL. This is probably causeed by the nested select
 on
> locations does not return a records and this returns nulls for the
 requested
> fields.
>
> set verify off
> set echo off
>
> accept inc_location prompt 'Enter the Incorrect Location Name: '
> accept cor_location prompt 'Enter Correct Location Name: '
>
> update location
> set (new_location, lockey) = (select location, lockey
> from locations
> where location = ('&cor_location')
> AND
> location IS NOT NULL)
> where location = ('&inc_location');
>
> The questions is how do we get it to write zero rows when cor_location
 is
> not found in locations. I suspect this is a join problem but I can't
 figure
> out how to stop this happening
>

This is not join problem. This is subquery problem. To be exact, this is a missing subquery problem. To update 0 rows, you should have condition in where clause of update statement which is false. For example:

update location
set (new_location, lockey) = (select location, lockey

      from locations
      where locations = ('&cor_location')
      AND
      location IS NOT NULL)

where location = ('&inc_location')
and exists (select 1 from locations

            where locations = ('&cor_location')) ;

> --
>
> James Alexander Starritt (james_at_jamesstarritt.com)
>
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Feb 06 2001 - 09:54:56 CST