CODEWITHSUNDEEP
sql-serverdatabasesql-updatesql-insert
coding1223322
coding1223322

Reputation: 473

Update table to insert new row if it does not exist

id | Product    | PrdouctOption| ServiceId
1  | 1          | 1            | 12
2  | 2          | 1            | 12
3  | 1          | 1            | 13
4  | 2          | 1            | 13
5  | 1          | 2            | 14
6  | 1          | 1            | 15

How do I update all records in my table to insert a new row of product = 2 and productOption = 1 if it does not exist for a ServiceId. (in this case for serviceId 14 and 15)

I cannot seem to get a logic right.

So far this is what I have..

  UPDATE dbo.MyTable
  SET Product = 2, ProductOption = 1

//Can't figure out the logic for if it doesn't exist for a serviceid

Upvotes: 0

Views: 9704

Answers (2)

wvdz
wvdz

Reputation: 16641

A conditional insert could look something like this:

INSERT INTO table1 (Product,ProductOption, ServiceId)
SELECT DISTINCT 2,1, serviceId FROM Table1 t1
WHERE NOT EXISTS
  (SELECT 1 FROM table1
   WHERE product = 2
   AND ProductOption = 1
   AND ServiceId = t1.ServiceId)

Upvotes: 2

spencer7593
spencer7593

Reputation: 108400

An UPDATE statement affects rows that already exist in the table.

To add a new row to a table, you'd use an INSERT statement.

(Note that the UPDATE statement in the question will update every row in the table; there isn't any WHERE clause.)

Upvotes: 0

Related Questions