0

I am creating a simple table and adding a key constraint to make sure I have no duplicates when I update the table. 

CREATE TABLE [TableName](
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [Phone] [varchar](50) NOT NULL
);
ALTER TABLE [TableName]
ADD CONSTRAINT unique_row UNIQUE (
[FirstName],[LastName]
);

Assume I have a row Jack Smith 999-999-999 and I am loading Jack Smith 888-888-888, the second choice won't be loaded due to key constraint.

Is there a way to specify a table to store new value and delete the old value.

AK9309
  • 761
  • 3
  • 13
  • 33
  • a table ? You want to do that with your query (e.g. `UPDATE [TableName] SET [Phone] = '888-888-888' WHERE [FirstName] = 'Jack' AND [LastName] = 'Smith'; ` – mugabits Jun 01 '16 at 15:36
  • 1
    The table itself will not do that. You can do what is called an upsert command. Search on upsert. It uses merge. – paparazzo Jun 01 '16 at 15:39
  • @jmugz3 That would work for a simple case, however if my data table has 10000 rows, and I am loading 1000 new rows, where some are unique and some are not ,that wouldn't work – AK9309 Jun 01 '16 at 15:39
  • Answer is only as good as the question. Not clear where this data is coming from. Loading 1000 new rows is not very specific. – paparazzo Jun 01 '16 at 15:49
  • Possible duplicate of [syntax for single row MERGE / upsert in SQL Server](http://stackoverflow.com/questions/2479488/syntax-for-single-row-merge-upsert-in-sql-server) – paparazzo Jun 01 '16 at 15:50
  • @Paparazzi Thanks for your answer, I will look into `upsert` – AK9309 Jun 01 '16 at 15:57

0 Answers0