{"id":18630,"date":"2022-04-06T06:10:00","date_gmt":"2022-04-06T04:10:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=18630"},"modified":"2022-11-28T12:01:46","modified_gmt":"2022-11-28T11:01:46","slug":"update-same-column-another-line-sql","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/update-same-column-another-line-sql\/","title":{"rendered":"Update the same column of another line with SQL Server"},"content":{"rendered":"\n

How to update the same column in the same table of another line with SQL Server?<\/em><\/strong><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

A simple SQL Server update query updates all rows<\/h2>\n\n\n\n

Indeed, a standard query with the SQL UPDATE<\/a> 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.<\/p>\n\n\n\n

-- This query does not work because it is a simple SQL update\nUPDATE MyTable\nSET MyField1 = 'Value'\nWHERE MyKeyField = 'My Key';\n<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
Create the table to update beforehand<\/h2>\n\n\n\n
Create beforehand the example table, CUSTOMERS, whose full script is available on this tutorial, create the table of customers under SQL Server<\/a>.<\/p>\n\n\n\n
-- Test if the table CUSTOMERS already exists\n-- And delete it if necessary\nIF EXISTS(\n   SELECT 1 FROM sys.objects\n   WHERE  object_id = object_id(N'[dbo].[CUSTOMERS]')\n      AND type in (N'U')\n)\nDROP TABLE [dbo].[CUSTOMERS]\nGO\n\n-- Create the CUSTOMERS table with the column NAME declared as UNIQUE\n-- The UNIQUE keyword defines the column with a unique value\n-- Inserting two customers with the same name is therefore impossible\nCREATE TABLE [dbo].[CUSTOMERS] (\n   [CLIENTID] int IDENTITY(1,1),\n   [NAME] nvarchar(20) UNIQUE,\n   [CITY] nvarchar(20)\n)\nGO\n\n-- Insert data for manipulation examples\nINSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('MAMMADOU', 'Lyon');\nINSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('SERGEI', 'Lyon');\nINSERT INTO dbo.CUSTOMERS (NAME, CITY) VALUES ('CHRISTOPHE', 'Paris');\n<\/pre>\n\n\n\n
Once the table is created in the database and the lines are inserted, check its content with a simple SELECT query.<\/p>\n\n\n\n
-- Check updated rows\nSELECT *\nFROM dbo.CUSTOMERS;<\/pre>\n\n\n
\n
\"Select
Select data from the SQL Server table data before the update<\/strong><\/figcaption><\/figure><\/div>\n\n\n
Update the same column on another row in MS SQL<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n