CODEWITHSUNDEEP
sqloracle-database
Lemec Cinq
Lemec Cinq

Reputation: 301

sql Oracle : Insert a value from another table only when value exists

I would like to insert in a table1 a value that comes from another table2 only when value returned from table2 is not null

I would look like to Something like this

  INSERT INTO TABLE1
                ( IMG_CODE, NAME, AMOUNT) 
                VALUES ((SELECT MAX(IM_CODE)  FROM IMAGE  WHERE imf_file_name = 'dfkdf4564654.jpg'), 'Meal', 154 )
                WHERE  ((SELECT MAX(IMF_CODE) FROM IMAGE  WHERE imf_file_name = 'dfkdf4564654.jpg') IS NOT NULL);

This request raise an error ORA-00933 - "SQL command not properly ended"

Do you know how to do this?

/

Upvotes: 0

Views: 202

Answers (2)

forpas
forpas

Reputation: 164089

You can do it with a nested subquery:

INSERT INTO TABLE1(IMG_CODE, NAME, AMOUNT) 
SELECT t.IM_CODE, 'Meal', 154
FROM (
  SELECT MAX(IM_CODE) IM_CODE FROM IMAGE  
  WHERE imf_file_name = 'dfkdf4564654.jpg'
) t
WHERE t.IM_CODE IS NOT NULL

If MAX(IM_CODE) is NULL then the row will not be inserted.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269643

How about this?

INSERT INTO TABLE1 ( IMG_CODE, NAME, AMOUNT) 
    SELECT MAX(IM_CODE), 'Meal', 154 
    FROM IMAGE  
    WHERE imf_file_name = 'dfkdf4564654.jpg'
    GROUP BY imf_file_name;

This is a little subtle. If nothing matches your query with no GROUP BY, then exactly one row is returned with a NULL value. With a GROUP BY, though, only matching groups are returned. So, if there are no rows, nothing gets inserted.

The advantage to this approach is that it readily extends to multiple file names.

Upvotes: 1

Related Questions