How to replace SELECT statement inside IF statement for it to work [duplicate]

Question

I have a simple question - for examples sake let's have the table

CITY(ID,Name).

An idea would be that when I want to add new city I first make sure it's not already in the table CITY.

Code example would be:

IF cityName NOT IN (SELECT name FROM city) THEN   
  INSERT INTO City(ID, NAME) VALUES(100, cityName); 
ELSE        
  Raise namingError;   
END IF;

However I can't have that subquery inside if statement so what should I replace it with? Any kind of list or variable or trick that I could use?

It's different issue here mate. Other topic is very nice but I need an option to first check and raise error when city exists, I couldn't find answer to my question in other one :( — devBem
– devBem, Commented Jan 2, 2014 at 15:48
Do you have any unique (natural) keys on the table at all? It's always better to let the DB do the work for you. If you don't then do you really need to raise an error? Does it matter if someone tries to create something that already exists? — Ben
– Ben, Commented Jan 2, 2014 at 15:58
It's task for my university and that's in it's description, theres only unique key on ID field and I'm supposed to handle naming errors by myself. — devBem
– devBem, Commented Jan 2, 2014 at 16:21

Community · Accepted Answer · 2017-05-23 12:11:34Z

2

 IF NOT EXISTS(SELECT 1 FROM CITY WHERE NAME = <CITYNAME>) 
    INSERT INTO City(ID, NAME) VALUES(100, cityName);

OR

 INSERT INTO City
  SELECT 100,'cityName'
   FROM dual 
         WHERE NOT EXISTS (SELECT 1
                 FROM CITY
                WHERE name = cityname
              )

I read the second query here

I don't have a database to try this out, but this should work

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jan 2, 2014 at 15:16

Adarsh

3,6592 gold badges23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Poole Over a year ago

The first statement isn't valid; you can't use exists outside SQL. The second statement would work (without the quotes around cityName since it's a PL/SQL variable); but to meet the exception requirement you'd need to test if sql%rowcount = 0 and raise the exception if so, as that would indicate no new row was inserted because the name already existed.

Joe W · Accepted Answer · 2014-01-02 15:27:30Z

You could use a merge command to perform the insert into the table. While the merge command is used to perform an insert if the data is not present or an update if the data is present in this case since you just have two fields it will just preform the insert for you.

This is useful if you want to take data from one or more tables and combine them into one.

MERGE INTO city c
    USING (SELECT * FROM city_import ) h
    ON (c.id = h.id and c.city = h.city)
  WHEN MATCHED THEN

  WHEN NOT MATCHED THEN
    INSERT (id, city)
    VALUES (h.id, h.city);

http://www.oracle-base.com/articles/9i/merge-statement.php

Wish I had enough reputation to upvote this as it's not answer to my question, yet it's very interesting stuff and it's useful.

Bob Jarvis - Слава Україні · Accepted Answer · 2014-01-02 16:43:28Z

1

If it was me I'd probably do something like

DECLARE
  rowCity  CITY%ROWTYPE;
BEGIN
  SELECT * INTO rowCity FROM CITY c WHERE c.NAME = cityName;

  -- If we get here it means the city already exists; thus, we raise an exception

  RAISE namingError;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- cityName not found in CITY; therefore we insert the necessary row
    INSERT INTO City(ID, NAME) VALUES(100, cityName);
END;

Share and enjoy.

answered Jan 2, 2014 at 16:43

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

1 Comment

devBem Over a year ago

Heh yeah that's precisely what I did but couldn't add answer to my own question. It works just fine, thanks for contribution.

MT0 · Accepted Answer · 2014-01-02 20:04:42Z

Two options:

One using INSERT INTO ... SELECT with a LEFT OUTER JOIN; and
The other using MERGE

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE city (
  ID   NUMBER(2) PRIMARY KEY,
  NAME VARCHAR2(20)
);

INSERT INTO city
SELECT 1, 'City Name' FROM DUAL;

CREATE TABLE city_errors (
  ID    NUMBER(2),
  NAME  VARCHAR2(20),
  TS    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ERROR VARCHAR2(20)
);

Query 1:

DECLARE
  city_id   CITY.ID%TYPE   := 2;
  city_name CITY.NAME%TYPE := 'City Name';

  namingError EXCEPTION;
  PRAGMA EXCEPTION_INIT( namingError, -20001 );
BEGIN
  INSERT INTO city ( id, name )
  SELECT city_id,
         city_name
  FROM   DUAL d
         LEFT OUTER JOIN
         city c
         ON ( c.name = city_name )
  WHERE  c.id IS NULL;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE namingError;
  END IF;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- Do something when duplicate ID found.
    INSERT INTO city_errors ( ID, NAME, ERROR ) VALUES ( city_id, city_name, 'Duplicate ID' );
  WHEN namingError THEN
    -- Do something when duplicate Name found.
    INSERT INTO city_errors ( ID, NAME, ERROR ) VALUES ( city_id, city_name, 'Duplicate Name' );
END;

Results:

Query 2:

DECLARE
  city_id   CITY.ID%TYPE   := 3;
  city_name CITY.NAME%TYPE := 'City Name';

  namingError EXCEPTION;
  PRAGMA EXCEPTION_INIT( namingError, -20001 );
BEGIN
  MERGE INTO city c
  USING ( SELECT city_id   AS id,
                 city_name AS name
          FROM DUAL ) d
  ON    ( c.Name = d.Name )
  WHEN NOT MATCHED THEN
    INSERT VALUES ( d.id, d.name );

  IF SQL%ROWCOUNT = 0 THEN
    RAISE namingError;
  END IF;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- Do something when duplicate ID found.
    INSERT INTO city_errors ( ID, NAME, ERROR ) VALUES ( city_id, city_name, 'Duplicate ID' );
  WHEN namingError THEN
    -- Do something when duplicate Name found.
    INSERT INTO city_errors ( ID, NAME, ERROR ) VALUES ( city_id, city_name, 'Duplicate Name' );
END;

Results:

Query 3:

SELECT * FROM City

Results:

| ID |      NAME |
|----|-----------|
|  1 | City Name |

Query 4:

SELECT * FROM City_Errors

Results:

| ID |      NAME |                             TS |          ERROR |
|----|-----------|--------------------------------|----------------|
|  2 | City Name | January, 02 2014 20:01:49+0000 | Duplicate Name |
|  3 | City Name | January, 02 2014 20:01:49+0000 | Duplicate Name |

Wow, that's quite bit more complex than what I did, I think I will stick with @BobJarvis answer but anyway it's very nice thing to know of and I will make use of it in future. Much apprieciated, thanks.

Collectives™ on Stack Overflow

How to replace SELECT statement inside IF statement for it to work [duplicate]

4 Answers 4

1 Comment

2 Comments

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

1 Comment

Linked

Related