Update the same column of another line with SQL Server

How to update the same column in the same table of another line with SQL Server?

Example of code to update the same SQL Server column on another line, on the same table. This row must be identified by another key, a simple UPDATE query of this type is not enough. Indeed, this query updates all rows with this key.

A simple SQL Server update query updates all rows

Indeed, a standard query with the SQL UPDATE command is not enough, because it updates all rows identified by the key or the filter, with the target value. Including the row with the source value. It is logical, because nothing is used to distinguish different lines in the following query.

-- This query does not work because it is a simple SQL update
UPDATE MyTable
SET MyField1 = 'Value'
WHERE MyKeyField = 'My Key';

So, to update the field with SQL Server, identify the tuple that contains the other value and then retrieve the value. This tutorial shows how to update a field from the value contained in another row of the same column, identified by a key.

Create the table to update beforehand

Create beforehand the example table, CUSTOMERS, whose full script is available on this tutorial, create the table of customers under SQL Server.

-- Test if the table CUSTOMERS already exists
-- And delete it if necessary
IF EXISTS(
   SELECT 1 FROM sys.objects
   WHERE  object_id = object_id(N'[dbo].[CUSTOMERS]')
      AND type in (N'U')
)
DROP TABLE [dbo].[CUSTOMERS]
GO

-- Create the CUSTOMERS table with the column NAME declared as UNIQUE
-- The UNIQUE keyword defines the column with a unique value
-- Inserting two customers with the same name is therefore impossible
CREATE TABLE [dbo].[CUSTOMERS] (
   [CLIENTID] int IDENTITY(1,1),
   [NAME] nvarchar(20) UNIQUE,
   [CITY] nvarchar(20)
)
GO

-- Insert data for manipulation examples
INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('MAMMADOU', 'Lyon');
INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('SERGEI', 'Lyon');
INSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('CHRISTOPHE', 'Paris');

Once the table is created in the database and the lines are inserted, check its content with a simple SELECT query.

-- Check updated rows
SELECT *
FROM dbo.CUSTOMERS;

**Select data from the SQL Server table data before the update**

Update the same column on another row in MS SQL

In order to retrieve the value of the field and update the column, use a query of this type. That is, you must use the same table twice with two different aliases:

C1 clients: the alias of the table to be updated
C2 clients: the alias of the same table to read the source value

Run this UPDATE query with SQL Server 2016, but it works with other versions, such as SQL Server 2012 or SQL Server 2019.

-- Update the customer table
-- I.e. the same column but in a different row
UPDATE C1
SET [CITY] = C2.[CITY]
FROM
  [dbo].[CUSTOMERS] C1 ,
  [dbo].[CUSTOMERS] C2
WHERE C1.[NAME] = 'MAMMADOU'
  AND C2.[NAME] = 'CHRISTOPHE';


-- Check updated rows
SELECT *
FROM dbo.CUSTOMERS;

Query to Update the same column of another line with SQL Server in SSMS — **Query to Update the same column of another line with SQL Server**

Note that the client MAMMADOU is now attached to Paris. In a query with aliases and joins, the fields of the table are accessible only by using the alias such as C1.CITY. Or the full name dbo.CUSTOMERS.CITY to distinguish the two calls to the same table. Thus the rules apply as in a classic SQL query with a join.

This tutorial on SQL Update query explains how, with a simple join, it is possible to retrieve the value of a row. Then how to use that value to update the same column but this time from a target row.