CODEWITHSUNDEEP
sqlsql-server
Zion
Zion

Reputation:

Using SQl Server CE; Possible to Insert Only If Not Exists and Delete if Exists?

I have a One Field Table in SQL CE which I need a SQL Statement for. The objective is to Delete the record if it already exists and insert the record if it does not exist. Is the possible with SQL CE?

INSERT INTO Source_Table
SELECT     'myvalue' AS Expr1
WHERE     (NOT EXISTS
                 (SELECT     Source_Data
                 FROM        Source_Table AS Source_Table_1
                 WHERE     (Source_Data = 'myvalue')))

Upvotes: 0

Views: 1152

Answers (2)

tpdi
tpdi

Reputation: 35171

If the aim is to literally delete if it exists and only insert it if it does not exist, then you need to do what you've written.

If the aim is to insert if it doesn't exist and replace if it does, Adam's correct that you may as well just always run the delete followed by the insert. I'd run both in the same statement batch, to save overhead.

Were there more than one field, of course you'd do an update, not a delete followed by insert. (But delete followed by insert is how Sybase internally performs updates.)

Upvotes: 0

Adam Robinson
Adam Robinson

Reputation: 185663

Why not just...

DELETE Source_Table WHERE Source_Data = 'myvalue'
GO
INSERT INTO Source_Table (Source_Data) values('myvalue')

I'm not sure what the point of deleting a record and then inserting the same data would be, but this should accomplish it.

Upvotes: 1

Related Questions