Updating Records And Table Variables

In a previous post, located here, we talked about inserting records into table variables. Then in this post we talked about deleting records from a table variable. Now I will illustrate updating records within a table variable. It is the same as updating records of normal tables, using an "UPDATE" statement:

1. USE master;
2. GO
3. -- The creation of our Table Variable.
4. DECLARE @UserAccounts TABLE
5. (
6.     firstName VARCHAR(50)
7.     , lastName VARCHAR(50)
8. );
10. -- Inserting 3 records into our Table Variable.
11. INSERT INTO @UserAccounts
12. VALUES ('John', 'Doe');
14. INSERT INTO @UserAccounts
15. VALUES ('Jane', 'Doe');
17. INSERT INTO @UserAccounts
18. VALUES ('Jim', 'Doe');
20. -- Update a record from our Table Variable.
21. UPDATE @UserAccounts
22. SET firstName = 'This is an unrealistic name.'
23. WHERE firstName = 'Jim';
25. -- Selecting the records from our Table Variable.
26. SELECT * FROM @UserAccounts;

The result of this query should be:


(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
firstName                                          lastName
-------------------------------------------------- --------------------------------------------------
John                                               Doe
Jane                                               Doe
This is an unrealistic name.                       Doe

(3 row(s) affected)