INSERT with IF Exists in SQL, SQLite

Question

how to check the value exist before insert?

INSERT INTO DeliveryOrder (ContactName, ContactID) VALUES ('Andy', (SELECT ContactID FROM Contact WHERE ContactName = 'Andy'))

I want to verify whether the ContactID is exist before insert the record.

Had try below but not working:

INSERT INTO DeliveryOrder (ContactName, ContactID) VALUES ('Andy', (SELECT ContactID FROM Contact WHERE ContactName = 'Andy')) WHERE IF Exists (SELECT ContactID FROM Contact WHERE ContactName = 'Andy')

You could check before hand (in a transaction), or you might find INSERT OR REPLACE or INSERT OR IGNORE to be useful (see SQLite docs). — TripeHound
– TripeHound, Commented May 4, 2017 at 9:45
Do not check for existence. Define a unique constrain and ignore constrain violation errors during import. — ceving
– ceving, Commented May 4, 2017 at 10:07

Mike · Accepted Answer · 2017-05-05 06:16:25Z

2

INSERT INTO DeliveryOrder (ContactName, ContactID)
 select 'Andy', ContactID
   from Contact
  WHERE ContactName = 'Andy'

edited May 5, 2017 at 6:16

answered May 4, 2017 at 10:21

Mike

2,0051 gold badge10 silver badges15 bronze badges

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

3 Comments

Steve Wallace Over a year ago

this woks better - it helps to think of your insert as recording your select - so if you write the select statement to return the values you need first then the Insert statement appears above it (with difficulties around sub queries)

user14351 Over a year ago

but my contactname will be another fix value, then contactID will be take from a table.

Mike Over a year ago

@user14351 You can specify any values that are not in the table in the clause"select". Example updated.

Pradip Meghapra · Accepted Answer · 2017-05-04 14:11:35Z

0

This will help you

IF  EXISTS (SELECT ContactID
        FROM Contact  c
        WHERE
            c.ContactName ='Andy'
        )
Insert Into DeliveryOrder (ContactName, ContactID)
        VALUES ('Andy', (SELECT top 1 ContactID FROM Contact WHERE ContactName = 'Andy'))

Try this

answered May 4, 2017 at 14:11

Pradip Meghapra

1485 bronze badges

Collectives™ on Stack Overflow

INSERT with IF Exists in SQL, SQLite

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related