Oracle SQL problem giving me a headache!

From: James Alexander Starritt <james_at_jamesstarritt.com>
Date: Mon, 5 Feb 2001 14:16:48 -0600
Message-ID: <95n1pa$hv5v1$1@ID-68406.news.dfncis.de>

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

--

James Alexander Starritt (james_at_jamesstarritt.com)

Received on Mon Feb 05 2001 - 14:16:48 CST