CODEWITHSUNDEEP
sqlsql-server-2008
user1652427
user1652427

Reputation: 697

UPDATE rows that exist, INSERT rows that do not

I have two tables. Users and UserMap. I need something like this...

INSERT INTO USERS (USER_ID, VALUE_TYPE, SOME_VALUE)
SELECT USERS.ID, 0, 1

The problem is that I would like to update rather than insert if the user and type already exists. This is my solution.

DELETE FROM USERS
WHERE USERS.VALUE_TYPE = 0

INSERT INTO USERS (USER_ID, VALUE_TYPE, SOME_VALUE)
SELECT USERS.ID, 0, 1
FROM USERS

This works, I was just looking for something better if it exists. Any recommendations?

Edit. Whoops, I typed this incorrectly. Sorry for the confusion, and thanks for all the help.

Upvotes: 1

Views: 96

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

What you're currently doing is very expensive. What if you have a million rows and only four rows have changed? What if no rows have changed?

The typical model is something like:

-- first, update the rows that match
UPDATE u
  SET SOME_VALUE = m.SOME_VALUE
  FROM dbo.Users AS u
  INNER JOIN dbo.UserMap AS m
  ON u.ID = m.USER_ID
  AND u.VALUE_TYPE = m.VALUE_TYPE
  WHERE u.VALUE_TYPE = 0;

-- next, add the rows that don't match
INSERT dbo.Users(USER_ID, VALUE_TYPE, SOME_VALUE)
  SELECT m.USER_ID, 0, 1
  FROM dbo.UserMap AS m
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM dbo.Users
      WHERE ID = m.USER_ID
      AND VALUE_TYPE = 0
  );

You can also use MERGE but personally I find the syntax quite daunting. Also, I'm not sure I trust that all of the bugs that have been reported against it have been resolved. (Different link.)

Upvotes: 4

Related Questions