Apr 3, 2012

The column name '' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name '' may appear twice in the view definition.


Suppose we have Employee table in current database. If we will execute following sql query in sql server:

UPDATE Employee SET
    Name = 'Scott',
    Name = 'Marry'

Or

CREATE VIEW vwEmp
AS(
    SELECT
         ID,
         Name,
         Name AS LocalName
    FROM Employee
)

UPDATE vwEmp SET
    Name = 'Scott',
    LocalName = 'S'

We will get error message :

The column name '' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name '' may appear twice in the view definition.

Cause: We can to update a same column more than one times in UPDATE statement in sql server.

Solution:

Set the value of any column of a table in UPDATE statement only times. For example:

UPDATE Employee SET
    Name = 'Scott'

Or

UPDATE vwEmp SET
    Name = 'Scott'

3 comments:

  1. Anonymous2/15/2013

    Thanks. You made this easy to understand.

    ReplyDelete
  2. Lol... Extraordinary tricky solution.

    ReplyDelete
  3. The column name 'benchmark' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

    ReplyDelete