How to replace SELECT statement inside IF statement for it to work

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, Jan 02 '14 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, Jan 02 '14 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, Jan 02 '14 at 16:21

score 2 · Answer 1 · edited May 23 '17 at 12:11

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 '17 at 12:11

Community

1
1

answered Jan 02 '14 at 15:16

Adarsh

3,613
2
21
37

1

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. – Alex Poole Jan 02 '14 at 19:18

score 2 · Answer 2 · answered Jan 02 '14 at 15:27

2

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

answered Jan 02 '14 at 15:27

Joe W

1,767
2
28
37

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. – devBem Jan 02 '14 at 15:49
@Roff Glad you find it useful at least! – Joe W Jan 02 '14 at 15:58

score 1 · Accepted Answer · answered Jan 02 '14 at 16:43

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 02 '14 at 16:43

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

48,992
9
77
110

Heh yeah that's precisely what I did but couldn't add answer to my own question. It works just fine, thanks for contribution. – devBem Jan 02 '14 at 18:38

MT0 · Answer 4 · 2014-01-02T20:04:42.697

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. — devBem, Jan 02 '14 at 20:40

How to replace SELECT statement inside IF statement for it to work

4 Answers4