Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Oracle SQL problem giving me a headache!
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)
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