Update table to insert new row if it does not exist

Question

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

You want to perform an `UPDATE` or an `INSERT` or maybe both? — Giorgos Betsos, Jan 14 '15 at 14:18
And which DBMS are you using? Either mysql or sql-server, they are not the same thing. — Sean Lange, Jan 14 '15 at 14:19
I think you want to insert a new record for existing service id's that don't have a product 2 product option 1... is that right? — xQbert, Jan 14 '15 at 14:20
Sorry, It would be Insert in to my table if it does not exist — coding1223322, Jan 14 '15 at 14:20
possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — Tab Alleman, Jan 14 '15 at 14:25

wvdz · Accepted Answer · 2015-01-14T14:35:14.977

2

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)

edited Jan 14 '15 at 14:35

answered Jan 14 '15 at 14:21

wvdz

16,251
4
53
90

This partially works.. it does not give the ServiceId to the new row. – coding1223322 Jan 14 '15 at 14:31
Ah, now I finally get what you're trying to do. Please review my revised answer. – wvdz Jan 14 '15 at 14:36
That has created duplications. It has inserted new rows for the ones that already exist. – coding1223322 Jan 14 '15 at 15:02
I don't understand. The distinct keyword prevents that more than 1 insert is done for a serviceID. What duplicates do you mean? – wvdz Jan 14 '15 at 15:26
Sorry my bad, didn't mention ServiceId was a linked table. Had to tweak it a little bit but they above works :) – coding1223322 Jan 14 '15 at 16:46

score 0 · Answer 2 · answered Jan 14 '15 at 14:21

0

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.)

answered Jan 14 '15 at 14:21

spencer7593

106,611
15
112
140

Update table to insert new row if it does not exist

2 Answers2