sql-servert-sqlsql-update

How to insert different values in a newly added column in a single update statement?


In t-SQL, I have added a new column named "AGE" to table "employees", but I have to write different update statements for insert each age value, since each employee has a different age.

Is there any way to insert different ages values into a single update statement? I cannot make use of default value or where condition, because there is no pattern in age of employees.


Solution

  • Keeping a changing value in the database is not a great idea : each day someone may have his age change. The birthdate does not change.

    For the technical side of the question :

    1. If you want to insert/update in "one" time, you have 2 options :
      Make several insert/update inside a transaction that you can rollback is case of error

    2. Construct a table then insert/update the content :

      UPDATE emp
      SET emp.Age = tmp.Age
      FROM dbo.Employee AS emp
      INNER JOIN (VALUES('name1', 25), ('name2', 52), ('name3', 34)) AS tmp(lastname, age)
            ON tmp.lastname = emp.lastname