Situation:
Suppose you have to update table with the data from another unrelated table
(or more likely, xlsx file).
Example:
ID | Name | Description |
---|---|---|
1 | Azrael | Tall, blue eyes |
2 | Kwanza | Short, slim |
3 | Lesedi | Dark eyes, long hair |
4 | Shahnaz | Long beard |
The Description column should be updated from the second table (excel, csv file) based on ID, or Name or whatever, as long as that column is unique. So, ID in this case.
ID | Description |
---|---|
4 | Trimmed beard |
3 | Short hair, slim |
1 | Light eyes |
Now to update each description with matching ID
-- Import data to temporary table for checking later
SELECT *
INTO #oldUsers
FROM dbo.Users
-- Update dbo.Users table
UPDATE dbo.Users
SET Description = dbo.excel.Description
FROM dbo.Users AS u
INNER JOIN dbo.excel ON dbo.excel.ID = u.ID
WHERE dbo.excel.ID = u.ID
-- Check if number of updated rows is right
SELECT * FROM dbo.Users
EXCEPT
SELECT * FROM #oldUsers
-- Drop temporary table
DROP TABLE #oldUsers
If the above doesn't work, here's an alternative:
MERGE INTO dbo.Users u
USING dbo.excel ex
ON u.ID = ex.ID
WHEN MATCHED THEN
UPDATE
SET u.Description = ex.Description;