How To Update A Column In Sql

UPDATE (Transact-SQL)

Commodity
07/12/2021
38 minutes to read

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform Organisation (PDW)

Changes existing data in a table or view in SQL Server. For examples, come across Examples.

Topic link icon Transact-SQL Syntax Conventions

Syntax

              -- Syntax for SQL Server and Azure SQL Database    [ WITH <common_table_expression> [...due north] ]   UPDATE        [ Top ( expression ) [ PERCENT ] ]        { { table_alias | <object> | rowset_function_limited             [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]         }         | @table_variable           }       SET           { column_name = { expression | DEFAULT | Cipher }             | { udt_column_name.{ { property_name = expression                                   | field_name = expression }                                   | method_name ( argument [ ,...n ] )                                 }             }             | column_name { .WRITE ( expression , @Start , @Length ) }             | @variable = expression             | @variable = cavalcade = expression             | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression             | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression             | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression           } [ ,...n ]           [ <OUTPUT Clause> ]       [ FROM{ <table_source> } [ ,...n ] ]        [ WHERE { <search_condition>                | { [ Electric current OF                      { { [ GLOBAL ] cursor_name }                          | cursor_variable_name                      }                    ]                 }               }        ]        [ Selection ( <query_hint> [ ,...n ] ) ]   [ ; ]      <object> ::=   {        [ server_name . database_name . schema_name .        | database_name .[ schema_name ] .        | schema_name .       ]       table_or_view_name}

              -- Syntax for Azure Synapse Analysis  [ WITH <common_table_expression> [ ,...n ] ] UPDATE [ database_name . [ schema_name ] . | schema_name . ] table_name Set { column_name = { expression | NULL } } [ ,...due north ]   FROM [ database_name . [ schema_name ] . | schema_name . ] table_name    Bring together {<join_table_source>}[ ,...n ]  ON <join_condition> [ WHERE <search_condition> ]    [ OPTION ( LABEL = label_name ) ]   [;]    <join_table_source> ::=    {       [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ Every bit ] table_or_view_alias      [ <tablesample_clause>]       | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]   }

              -- Syntax for Parallel Data Warehouse  UPDATE [ database_name . [ schema_name ] . | schema_name . ] table_name    Set { column_name = { expression | NULL } } [ ,...n ]   [ FROM from_clause ]   [ WHERE <search_condition> ]    [ Selection ( LABEL = label_name ) ]   [;]

Arguments

WITH <common_table_expression>
Specifies the temporary named result set or view, also known equally common table expression (CTE), divers within the scope of the UPDATE statement. The CTE consequence set is derived from a simple query and is referenced by UPDATE statement.

Common tabular array expressions tin besides be used with the SELECT, INSERT, DELETE, and CREATE VIEW statements. For more information, see WITH common_table_expression (Transact-SQL).

Acme ( expression ) [ Percentage ]
Specifies the number or percent of rows that are updated. expression tin can be either a number or a percent of the rows.

The rows referenced in the Acme expression used with INSERT, UPDATE, or DELETE are non bundled in whatsoever gild.

Parentheses delimiting expression in Meridian are required in INSERT, UPDATE, and DELETE statements. For more information, see TOP (Transact-SQL).

table_alias
The allonym specified in the FROM clause representing the table or view from which the rows are to be updated.

server_name
Is the proper name of the server (using a linked server proper noun or the OPENDATASOURCE function every bit the server name) on which the table or view is located. If server_name is specified, database_name and schema_name are required.

database_name
Is the proper name of the database.

schema_name
Is the name of the schema to which the tabular array or view belongs.

table_or_view_name
Is the name of the table or view from which the rows are to be updated. The view referenced by table_or_view_name must exist updatable and reference exactly one base of operations table in the FROM clause of the view. For more information about updatable views, see CREATE VIEW (Transact-SQL).

rowset_function_limited
Is either the OPENQUERY or OPENROWSET part, field of study to provider capabilities.

WITH ( <Table_Hint_Limited> )
Specifies i or more than table hints that are immune for a target tabular array. The WITH keyword and the parentheses are required. NOLOCK, READUNCOMMITTED, and NOEXPAND are not immune. For information well-nigh tabular array hints, run across Table Hints (Transact-SQL).

@table_variable
Specifies a tabular array variable equally a table source.

SET
Specifies the listing of column or variable names to be updated.

column_name
Is a column that contains the data to be changed. column_name must exist in table_or view_name. Identity columns cannot be updated.

expression
Is a variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value. The value returned by expression replaces the existing value in column_name or @variable.

Note

When referencing the Unicode graphic symbol data types nchar, nvarchar, and ntext, 'expression' should be prefixed with the uppercase letter 'Northward'. If 'N' is not specified, SQL Server converts the string to the code folio that corresponds to the default collation of the database or column. Whatsoever characters not institute in this lawmaking page are lost.

DEFAULT
Specifies that the default value defined for the column is to supplant the existing value in the column. This can also be used to change the column to NULL if the column has no default and is defined to allow null values.

{ += | -= | *= | /= | %= | &= | ^= | |= }
Chemical compound assignment operator:
+= Add and assign
-= Subtract and assign
*= Multiply and assign
/= Divide and assign
%= Modulo and assign
&= Bitwise AND and assign
^= Bitwise XOR and assign
|= Bitwise OR and assign

udt_column_name
Is a user-defined type column.

property_name | field_name
Is a public holding or public data member of a user-divers type.

method_name ( argument [ ,... n] )
Is a nonstatic public mutator method of udt_column_name that takes 1 or more arguments.

.WRITE ( expression ,@Beginning ,@Length )
Specifies that a department of the value of column_name is to be modified. expression replaces @Length units starting from @Offset of column_name. Only columns of varchar(max), nvarchar(max), or varbinary(max) tin be specified with this clause. column_name cannot exist NULL and cannot be qualified with a table name or table alias.

expression is the value that is copied to column_name. expression must evaluate to or be able to be implicitly cast to the column_name type. If expression is set to Naught, @Length is ignored, and the value in column_name is truncated at the specified @Beginning.

@Beginning is the starting point in the value stored in column_name at which expression is written. @Offset is a zippo-based ordinal byte position, is bigint, and cannot exist a negative number. If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. If @Offset is greater than the byte length of the column_name value, the Database Engine returns an error. If @Outset plus @Length exceeds the cease of the underlying value in the column, the deletion occurs upwardly to the last grapheme of the value.

@Length is the length of the section in the cavalcade, starting from @Offset, that is replaced past expression. @Length is bigint and cannot exist a negative number. If @Length is NULL, the update operation removes all data from @Start to the stop of the column_name value.

For more information, run into Updating Large Value Information Types.

@ variable
Is a declared variable that is fix to the value returned by expression.

Gear up @ variable = cavalcade = expression sets the variable to the same value as the cavalcade. This differs from SET @ variable = column, cavalcade = expression, which sets the variable to the pre-update value of the cavalcade.

<OUTPUT_Clause>
Returns updated data or expressions based on it as part of the UPDATE operation. The OUTPUT clause is non supported in whatever DML statements that target remote tables or views. For more than data about the arguments and beliefs of this clause, meet OUTPUT Clause (Transact-SQL).

FROM <table_source>
Specifies that a table, view, or derived table source is used to provide the criteria for the update performance. For more data, see FROM (Transact-SQL).

If the object being updated is the same equally the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than in one case in the FROM clause, one, and but one, reference to the object must non specify a table alias. All other references to the object in the FROM clause must include an object allonym.

A view with an INSTEAD OF UPDATE trigger cannot exist a target of an UPDATE with a FROM clause.

Annotation

Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from whatsoever telephone call to these functions used as the target of the update, even if identical arguments are supplied to the ii calls. In particular, filter or bring together conditions applied on the consequence of one of those calls have no effect on the results of the other.

WHERE
Specifies the conditions that limit the rows that are updated. In that location are two forms of update based on which grade of the WHERE clause is used:

Searched updates specify a search condition to qualify the rows to delete.
Positioned updates utilise the Electric current OF clause to specify a cursor. The update functioning occurs at the current position of the cursor.

<search_condition>
Specifies the condition to exist met for the rows to exist updated. The search condition can also be the condition upon which a bring together is based. At that place is no limit to the number of predicates that tin can be included in a search condition. For more information about predicates and search weather, meet Search Condition (Transact-SQL).

Current OF
Specifies that the update is performed at the electric current position of the specified cursor.

A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. This can exist more authentic than a searched update that uses a WHERE <search_condition> clause to authorize the rows to exist updated. A searched update modifies multiple rows when the search condition does not uniquely identify a single row.

GLOBAL
Specifies that cursor_name refers to a global cursor.

cursor_name
Is the proper noun of the open cursor from which the fetch should be made. If both a global and a local cursor with the proper noun cursor_name be, this argument refers to the global cursor if GLOBAL is specified; otherwise, it refers to the local cursor. The cursor must allow updates.

cursor_variable_name
Is the proper name of a cursor variable. cursor_variable_name must reference a cursor that allows updates.

OPTION ( <query_hint> [ ,... northward ] )
Specifies that optimizer hints are used to customize the way the Database Engine processes the statement. For more data, see Query Hints (Transact-SQL).

Best practices

Employ the @@ROWCOUNT function to return the number of inserted rows to the customer application. For more information, encounter @@ROWCOUNT (Transact-SQL).

Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used but when the UPDATE argument affects a single tape. If the UPDATE statement affects multiple records, to return the former and new values for each record, apply the OUTPUT clause.

Employ caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the argument includes a FROM clause that is not specified in such a manner that just ane value is bachelor for each column occurrence that is updated, that is if the UPDATE argument is not deterministic. For example, in the UPDATE statement in the following script, both rows in Table1 run into the qualifications of the FROM clause in the UPDATE statement; but it is undefined which row from Table1 is used to update the row in Table2.

              Utilise AdventureWorks2012;   GO   IF OBJECT_ID ('dbo.Table1', 'U') IS Not NULL       DROP Tabular array dbo.Table1;   GO   IF OBJECT_ID ('dbo.Table2', 'U') IS Non NULL       Driblet TABLE dbo.Table2;   GO   CREATE TABLE dbo.Table1        (ColA INT NOT Cypher, ColB DECIMAL(10,iii) NOT NULL);   GO   CREATE TABLE dbo.Table2        (ColA INT Chief Cardinal Not NULL, ColB DECIMAL(10,iii) NOT Nothing);   GO   INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0);   INSERT INTO dbo.Table2 VALUES(ane, 0.0);   Go   UPDATE dbo.Table2    Prepare dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB   FROM dbo.Table2        INNER Join dbo.Table1        ON (dbo.Table2.ColA = dbo.Table1.ColA);   Go   SELECT ColA, ColB    FROM dbo.Table2;

The same problem tin can occur when the FROM and WHERE Current OF clauses are combined. In the post-obit example, both rows in Table2 run into the qualifications of the FROM clause in the UPDATE statement. It is undefined which row from Table2 is to be used to update the row in Table1.

              USE AdventureWorks2012;   Become   IF OBJECT_ID ('dbo.Table1', 'U') IS NOT Null       DROP TABLE dbo.Table1;   Become   IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL       Drib TABLE dbo.Table2;   Become   CREATE Tabular array dbo.Table1       (c1 INT PRIMARY Primal Not NULL, c2 INT Not Goose egg);   Become   CREATE Table dbo.Table2       (d1 INT PRIMARY KEY Not NULL, d2 INT Non NULL);   Go   INSERT INTO dbo.Table1 VALUES (ane, x);   INSERT INTO dbo.Table2 VALUES (i, xx), (2, 30);   Go   DECLARE abc CURSOR LOCAL FOR       SELECT c1, c2        FROM dbo.Table1;   Open abc;   FETCH abc;   UPDATE dbo.Table1    Set c2 = c2 + d2    FROM dbo.Table2    WHERE CURRENT OF abc;   Become   SELECT c1, c2 FROM dbo.Table1;   Become

Compatibility back up

Support for utilise of the READUNCOMMITTED and NOLOCK hints in the FROM clause that utilise to the target tabular array of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development piece of work, and plan to modify applications that currently employ them.

Information types

All char and nchar columns are correct-padded to the defined length.

If ANSI_PADDING is gear up to OFF, all abaft spaces are removed from data inserted into varchar and nvarchar columns, except in strings that contain only spaces. These strings are truncated to an empty cord. If ANSI_PADDING is prepare to ON, abaft spaces are inserted. The Microsoft SQL Server ODBC driver and OLE DB Provider for SQL Server automatically gear up ANSI_PADDING ON for each connection. This can exist configured in ODBC data sources or by setting connection attributes or backdrop. For more data, encounter SET ANSI_PADDING (Transact-SQL).

Updating text, ntext, and image columns

Modifying a text, ntext, or epitome column with UPDATE initializes the column, assigns a valid text pointer to information technology, and allocates at least one data page, unless the cavalcade is being updated with NULL.

To replace or change large blocks of text, ntext, or image information, use WRITETEXT or UPDATETEXT instead of the UPDATE statement.

If the UPDATE statement could change more than one row while updating both the clustering central and one or more text, ntext, or image columns, the fractional update to these columns is executed every bit a full replacement of the values.

Important

The ntext, text, and epitome data types will be removed in a hereafter version of MicrosoftSQL Server. Avert using these data types in new development work, and programme to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Updating large value data types

Utilize the .WRITE ( expression ,@Offset ,@Length ) clause to perform a partial or full update of varchar(max), nvarchar(max), and varbinary(max) information types.

For example, a fractional update of a varchar(max) column might delete or modify only the offset 200 bytes of the column (200 characters if using ASCII characters), whereas a full update would delete or modify all the data in the column. .WRITE updates that insert or append new data are minimally logged if the database recovery model is gear up to bulk-logged or unproblematic. Minimal logging is not used when existing values are updated. For more information, run into The Transaction Log (SQL Server).

The Database Engine converts a partial update to a full update when the UPDATE statement causes either of these actions:

Changes a key column of the partitioned view or tabular array.
Modifies more than i row and likewise updates the key of a nonunique clustered index to a nonconstant value.

You cannot utilise the .WRITE clause to update a NULL cavalcade or set the value of column_name to Nil.

@Offset and @Length are specified in bytes for varbinary and varchar data types and in byte-pairs for the nvarchar data type. For more information on string data type lengths, see char and varchar (Transact-SQL) and nchar and nvarchar (Transact-SQL).

For best performance, we recommend that data be inserted or updated in chunk sizes that are multiples of 8040 bytes.

If the column modified past the .WRITE clause is referenced in an OUTPUT clause, the consummate value of the column, either the before image in deleted. column_name or the after image in inserted. column_name, is returned to the specified cavalcade in the table variable. Run into case R that follows.

To reach the aforementioned functionality of .WRITE with other character or binary data types, use the STUFF (Transact-SQL).

Updating User-defined Blazon columns

Updating values in user-defined type columns tin be accomplished in i of the following ways:

Supplying a value in a SQL Server system information type, equally long as the user-divers type supports implicit or explicit conversion from that type. The following example shows how to update a value in a cavalcade of user-divers type Point, past explicitly converting from a string.
```
                  UPDATE Cities   Prepare Location = CONVERT(Point, '12.3:46.2')   WHERE Proper noun = 'Anchorage';                                  
```
Invoking a method, marked every bit a mutator, of the user-defined type, to perform the update. The following example invokes a mutator method of blazon Point named SetXY. This updates the land of the instance of the type.
```
                  UPDATE Cities   Ready Location.SetXY(23.5, 23.5)   WHERE Name = 'Anchorage';                                  
```
Annotation

SQL Server returns an error if a mutator method is invoked on a Transact-SQL null value, or if a new value produced past a mutator method is nothing.
Modifying the value of a registered holding or public data fellow member of the user-divers type. The expression supplying the value must exist implicitly convertible to the type of the property. The following example modifies the value of property X of user-divers type Point.
```
                  UPDATE Cities   SET Location.X = 23.5   WHERE Name = 'Anchorage';                                  
```
To change dissimilar properties of the same user-defined type cavalcade, event multiple UPDATE statements, or invoke a mutator method of the type.

Updating FILESTREAM data

You lot can use the UPDATE statement to update a FILESTREAM field to a naught value, empty value, or a relatively small amount of inline data. Even so, a large corporeality of information is more efficiently streamed into a file by using Win32 interfaces. When you update a FILESTREAM field, you lot modify the underlying Blob data in the file organization. When a FILESTREAM field is fix to NULL, the BLOB data associated with the field is deleted. You cannot utilise .WRITE(), to perform fractional updates to FILESTREAM data. For more than information, run across FILESTREAM (SQL Server).

Fault treatment

If an update to a row violates a constraint or dominion, violates the Naught setting for the column, or the new value is an incompatible information type, the statement is canceled, an error is returned, and no records are updated.

When an UPDATE argument encounters an arithmetic error (overflow, divide by zero, or a domain error) during expression evaluation, the update is non performed. The rest of the batch is not executed, and an error message is returned.

If an update to a cavalcade or columns participating in a clustered index causes the size of the clustered index and the row to exceed eight,060 bytes, the update fails and an mistake bulletin is returned.

Interoperability

UPDATE statements are immune in the trunk of user-defined functions only if the table existence modified is a table variable.

When an INSTEAD OF trigger is defined on UPDATE actions against a table, the trigger is running instead of the UPDATE statement. Earlier versions of SQL Server simply support AFTER triggers defined on UPDATE and other data modification statements. The FROM clause cannot be specified in an UPDATE statement that references, either directly or indirectly, a view with an INSTEAD OF trigger divers on it. For more data about INSTEAD OF triggers, come across CREATE TRIGGER (Transact-SQL).

Limitations and restrictions

The FROM clause cannot be specified in an UPDATE statement that references, either directly or indirectly, a view that has an INSTEAD OF trigger defined on it. For more than information most INSTEAD OF triggers, meet CREATE TRIGGER (Transact-SQL).

When a mutual table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must lucifer. For example, if the CTE is assigned an allonym in the FROM clause, the alias must be used for all other references to the CTE. Unambiguous CTE references are required because a CTE does not have an object ID, which SQL Server uses to recognize the implicit relationship betwixt an object and its allonym. Without this human relationship, the query plan may produce unexpected join behavior and unintended query results. The following examples demonstrate correct and incorrect methods of specifying a CTE when the CTE is the target object of the update operation.

              Employ tempdb;   GO   -- UPDATE statement with CTE references that are correctly matched.   DECLARE @x Tabular array (ID INT, Value INT);   DECLARE @y TABLE (ID INT, Value INT);   INSERT @10 VALUES (one, x), (2, 20);   INSERT @y VALUES (1, 100),(two, 200);      WITH cte Every bit (SELECT * FROM @ten)   UPDATE x -- cte is referenced past the alias.   Fix Value = y.Value   FROM cte AS x  -- cte is assigned an alias.   INNER JOIN @y Every bit y ON y.ID = x.ID;   SELECT * FROM @10;   GO

Here is the result set.

              ID     Value   ------ -----   ane      100   2      200   (2 row(s) affected)

UPDATE argument with CTE references that are incorrectly matched.

              Employ tempdb;   Go   DECLARE @x TABLE (ID INT, Value INT);   DECLARE @y Table (ID INT, Value INT);   INSERT @ten VALUES (i, 10), (2, 20);   INSERT @y VALUES (1, 100),(2, 200);      WITH cte As (SELECT * FROM @x)   UPDATE cte   -- cte is not referenced past the alias.   Fix Value = y.Value   FROM cte Equally x  -- cte is assigned an allonym.   INNER JOIN @y As y ON y.ID = x.ID;    SELECT * FROM @10;    GO

Here is the result set.

              ID     Value   ------ -----   1      100   2      100   (ii row(s) affected)

Locking behavior

An UPDATE statement acquires an exclusive (X) lock on whatever rows that information technology modifies, and holds these locks until the transaction completes. Depending on the query program for the UPDATE statement, the number of rows being modified, and the isolation level of the transaction, locks may be acquired at the PAGE level or Tabular array level rather than the ROW level. To avoid these higher level locks, consider dividing update statements that affect thousands of rows or more into batches, and ensure that whatever join and filter conditions are supported past indexes. Come across the commodity on Locking in the Database Engine for more details on locking mechanics in SQL Server.

Logging behavior

The UPDATE statement is logged; nevertheless, fractional updates to large value data types using the .WRITE clause are minimally logged. For more information, run into "Updating Big Value Data Types" in the earlier department "Data Types".

Security

Permissions

UPDATE permissions are required on the target tabular array. SELECT permissions are also required for the table beingness updated if the UPDATE argument contains a WHERE clause, or if expression in the SET clause uses a cavalcade in the table.

UPDATE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the tabular array owner. Members of the sysadmin, db_owner, and db_securityadmin roles, and the tabular array owner tin transfer permissions to other users.

Examples

Category	Featured syntax elements
Basic Syntax	UPDATE
Limiting the Rows that Are Updated	WHERE • Superlative • WITH common table expression • WHERE CURRENT OF
Setting Column Values	computed values • compound operators • default values • subqueries
Specifying Target Objects Other than Standard Tables	views • tabular array variables • table aliases
Updating Data Based on Information From Other Tables	FROM
Updating Rows in a Remote Tabular array	linked server • OPENQUERY • OPENDATASOURCE
Updating Large Object Data Types	.WRITE • OPENROWSET
Updating User-defined Types	user-divers types
Overriding the Default Behavior of the Query Optimizer by Using Hints	table hints • query hints
Capturing the Results of the UPDATE Statement	OUTPUT clause
Using UPDATE in Other Statements	Stored Procedures • TRY...Catch

Basic syntax

Examples in this section demonstrate the bones functionality of the UPDATE statement using the minimum required syntax.

A. Using a unproblematic UPDATE argument

The following case updates a unmarried cavalcade for all rows in the Person.Address table.

              USE AdventureWorks2012;   GO   UPDATE Person.Address   SET ModifiedDate = GETDATE();

B. Updating multiple columns

The post-obit example updates the values in the Bonus, CommissionPct, and SalesQuota columns for all rows in the SalesPerson table.

              USE AdventureWorks2012;   GO   UPDATE Sales.SalesPerson   Fix Bonus = 6000, CommissionPct = .ten, SalesQuota = Nada;   Become

Limiting the Rows that Are Updated

Examples in this section demonstrate ways that y'all can utilize to limit the number of rows affected by the UPDATE argument.

C. Using the WHERE clause

The following instance uses the WHERE clause to specify which rows to update. The argument updates the value in the Color column of the Product.Production table for all rows that have an existing value of 'Red' in the Color column and have a value in the Name column that starts with 'Route-250'.

              Use AdventureWorks2012;   Get   UPDATE Production.Product   SET Color = N'Metallic Red'   WHERE Proper name Like N'Road-250%' AND Colour = N'Scarlet';   Become

D. Using the Peak clause

The post-obit examples use the TOP clause to limit the number of rows that are modified in an UPDATE statement. When a TOP (n) clause is used with UPDATE, the update operation is performed on a random option of 'north' number of rows. The post-obit example updates the VacationHours column by 25 percent for 10 random rows in the Employee table.

              USE AdventureWorks2012; GO UPDATE TOP (x) HumanResources.Employee SET VacationHours = VacationHours * 1.25 ; GO

If you must utilise Superlative to apply updates in a meaningful chronology, you must use TOP together with Order BY in a subselect statement. The following example updates the holiday hours of the x employees with the earliest rent dates.

              UPDATE HumanResources.Employee   SET VacationHours = VacationHours + eight   FROM (SELECT Top 10 BusinessEntityID FROM HumanResources.Employee        Social club By HireDate ASC) AS th   WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;   Go

E. Using the WITH common_table_expression clause

The following example updates the PerAssemblyQty value for all parts and components that are used straight or indirectly to create the ProductAssemblyID 800. The common table expression returns a hierarchical list of parts that are used directly to build ProductAssemblyID 800 and parts that are used to build those components, and then on. Only the rows returned by the mutual table expression are modified.

              Utilise AdventureWorks2012;   Go   WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS   (       SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,           b.EndDate, 0 AS ComponentLevel       FROM Production.BillOfMaterials Every bit b       WHERE b.ProductAssemblyID = 800             AND b.EndDate IS Zero       Spousal relationship ALL       SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,           bom.EndDate, ComponentLevel + 1       FROM Production.BillOfMaterials Equally bom            INNER JOIN Parts Equally p           ON bom.ProductAssemblyID = p.ComponentID           AND bom.EndDate IS NULL   )   UPDATE Production.BillOfMaterials   Prepare PerAssemblyQty = c.PerAssemblyQty * 2   FROM Product.BillOfMaterials AS c   JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID   WHERE d.ComponentLevel = 0;

F. Using the WHERE Current OF clause

The following example uses the WHERE CURRENT OF clause to update simply the row on which the cursor is positioned. When a cursor is based on a join, merely the table_name specified in the UPDATE argument is modified. Other tables participating in the cursor are non afflicted.

              Utilise AdventureWorks2012;   GO   DECLARE complex_cursor CURSOR FOR       SELECT a.BusinessEntityID       FROM HumanResources.EmployeePayHistory AS a       WHERE RateChangeDate <>             (SELECT MAX(RateChangeDate)             FROM HumanResources.EmployeePayHistory As b             WHERE a.BusinessEntityID = b.BusinessEntityID) ;   Open complex_cursor;   FETCH FROM complex_cursor;   UPDATE HumanResources.EmployeePayHistory   SET PayFrequency = 2    WHERE CURRENT OF complex_cursor;   Shut complex_cursor;   DEALLOCATE complex_cursor;   Go

Setting Column Values

Examples in this department demonstrate updating columns by using computed values, subqueries, and DEFAULT values.

Grand. Specifying a computed value

The following examples uses computed values in an UPDATE argument. The example doubles the value in the ListPrice cavalcade for all rows in the Product table.

              USE AdventureWorks2012 ;   Get   UPDATE Product.Product   Prepare ListPrice = ListPrice * 2;   Get

H. Specifying a chemical compound operator

The following example uses the variable @NewPrice to increase the cost of all ruddy bicycles by taking the current price and adding 10 to it.

              Employ AdventureWorks2012;   Go   DECLARE @NewPrice INT = ten;   UPDATE Production.Production   Prepare ListPrice += @NewPrice   WHERE Colour = Due north'Red';   Get

The post-obit example uses the compound operator += to append the information ' - tool malfunction' to the existing value in the column Proper noun for rows that accept a ScrapReasonID betwixt 10 and 12.

              Utilize AdventureWorks2012;   GO   UPDATE Production.ScrapReason    SET Name += ' - tool malfunction'   WHERE ScrapReasonID BETWEEN 10 and 12;

I. Specifying a subquery in the Ready clause

The following example uses a subquery in the Prepare clause to determine the value that is used to update the column. The subquery must render just a scalar value (that is, a unmarried value per row). The example modifies the SalesYTD cavalcade in the SalesPerson table to reflect the almost contempo sales recorded in the SalesOrderHeader table. The subquery aggregates the sales for each salesperson in the UPDATE argument.

              USE AdventureWorks2012;   Become   UPDATE Sales.SalesPerson   Prepare SalesYTD = SalesYTD +        (SELECT SUM(so.SubTotal)         FROM Sales.SalesOrderHeader AS so        WHERE so.OrderDate = (SELECT MAX(OrderDate)                              FROM Sales.SalesOrderHeader AS so2                              WHERE so2.SalesPersonID = and then.SalesPersonID)        AND Sales.SalesPerson.BusinessEntityID = and so.SalesPersonID        GROUP BY so.SalesPersonID);   GO

J. Updating rows using DEFAULT values

The post-obit example sets the CostRate column to its default value (0.00) for all rows that have a CostRate value greater than 20.00.

              USE AdventureWorks2012;   GO   UPDATE Production.Location   Set up CostRate = DEFAULT   WHERE CostRate > twenty.00;

Specifying Target Objects Other Than Standard Tables

Examples in this section demonstrate how to update rows past specifying a view, tabular array alias, or table variable.

K. Specifying a view equally the target object

The following example updates rows in a table past specifying a view as the target object. The view definition references multiple tables, however, the UPDATE statement succeeds because it references columns from only one of the underlying tables. The UPDATE statement would fail if columns from both tables were specified. For more information, see Modify Data Through a View.

              Utilize AdventureWorks2012;   Get   UPDATE Person.vStateProvinceCountryRegion   Set up CountryRegionName = 'United states of america of America'   WHERE CountryRegionName = 'United states';

L. Specifying a tabular array alias as the target object

The follow instance updates rows in the table Production.ScrapReason. The table allonym assigned to ScrapReason in the FROM clause is specified as the target object in the UPDATE clause.

              Apply AdventureWorks2012;   GO   UPDATE sr   SET sr.Name += ' - tool malfunction'   FROM Production.ScrapReason Equally sr   Bring together Product.WorkOrder Equally wo         ON sr.ScrapReasonID = wo.ScrapReasonID        AND wo.ScrappedQty > 300;

Chiliad. Specifying a table variable as the target object

The following example updates rows in a table variable.

              Apply AdventureWorks2012;   GO   -- Create the table variable.   DECLARE @MyTableVar Tabular array (       EmpID INT NOT Zero,       NewVacationHours INT,       ModifiedDate DATETIME);      -- Populate the table variable with employee ID values from HumanResources.Employee.   INSERT INTO @MyTableVar (EmpID)       SELECT BusinessEntityID FROM HumanResources.Employee;      -- Update columns in the table variable.   UPDATE @MyTableVar   Set up NewVacationHours = eastward.VacationHours + 20,       ModifiedDate = GETDATE()   FROM HumanResources.Employee AS e    WHERE east.BusinessEntityID = EmpID;      -- Display the results of the UPDATE argument.   SELECT EmpID, NewVacationHours, ModifiedDate FROM @MyTableVar   Club Past EmpID;   GO

Updating Data Based on Data From Other Tables

Examples in this section demonstrate methods of updating rows from one tabular array based on data in another table.

N. Using the UPDATE statement with information from another tabular array

The following instance modifies the SalesYTD column in the SalesPerson tabular array to reflect the most recent sales recorded in the SalesOrderHeader tabular array.

              Apply AdventureWorks2012;   GO   UPDATE Sales.SalesPerson   SET SalesYTD = SalesYTD + SubTotal   FROM Sales.SalesPerson Equally sp   Bring together Sales.SalesOrderHeader AS then       ON sp.BusinessEntityID = and so.SalesPersonID       AND then.OrderDate = (SELECT MAX(OrderDate)                           FROM Sales.SalesOrderHeader                           WHERE SalesPersonID = sp.BusinessEntityID);   Get

The previous example assumes that only one auction is recorded for a specified salesperson on a specific engagement and that updates are current. If more than one sale for a specified salesperson tin be recorded on the same solar day, the instance shown does not piece of work correctly. The case runs without error, only each SalesYTD value is updated with only i sale, regardless of how many sales actually occurred on that twenty-four hour period. This is because a unmarried UPDATE statement never updates the same row ii times.

In the situation in which more than one sale for a specified salesperson tin can occur on the aforementioned 24-hour interval, all the sales for each sales person must be aggregated together within the UPDATE statement, as shown in the following example:

              Utilize AdventureWorks2012;   GO   UPDATE Sales.SalesPerson   Gear up SalesYTD = SalesYTD +        (SELECT SUM(and so.SubTotal)         FROM Sales.SalesOrderHeader AS so        WHERE so.OrderDate = (SELECT MAX(OrderDate)                              FROM Sales.SalesOrderHeader AS so2                              WHERE so2.SalesPersonID = so.SalesPersonID)        AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID        GROUP By so.SalesPersonID);   GO

Updating rows in a remote tabular array

Examples in this department demonstrate how to update rows in a remote target table by using a linked server or a rowset office to reference the remote table.

O. Updating information in a remote table by using a linked server

The following example updates a tabular array on a remote server. The case begins by creating a link to the remote information source by using sp_addlinkedserver. The linked server proper noun, MyLinkedServer, is and so specified as function of the four-part object proper name in the form server.catalog.schema.object. Note that you must specify a valid server name for @datasrc.

              USE master;   GO   -- Create a link to the remote data source.    -- Specify a valid server name for @datasrc every bit 'server_name' or 'server_nameinstance_name'.      EXEC sp_addlinkedserver @server = N'MyLinkedServer',       @srvproduct = Northward' ',       @provider = N'SQLNCLI10',        @datasrc = Due north'<server name>',       @itemize = N'AdventureWorks2012';   GO   Utilize AdventureWorks2012;   GO   -- Specify the remote information source using a 4-office name    -- in the form linked_server.catalog.schema.object.      UPDATE MyLinkedServer.AdventureWorks2012.HumanResources.Department   Fix GroupName = N'Public Relations'   WHERE DepartmentID = iv;

P. Updating information in a remote table by using the OPENQUERY part

The following example updates a row in a remote table past specifying the OPENQUERY rowset function. The linked server name created in the previous instance is used in this example.

              UPDATE OPENQUERY (MyLinkedServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = iv')    Ready GroupName = 'Sales and Marketing';

Q. Updating data in a remote table past using the OPENDATASOURCE function

The following example updates a row in a remote table by specifying the OPENDATASOURCE rowset function. Specify a valid server proper noun for the data source by using the format server_name or server_name\instance_name. You may need to configure the instance of SQL Server for Advertizement Hoc Distributed Queries. For more information, see advertisement hoc distributed queries Server Configuration Option.

              UPDATE OPENDATASOURCE('SQLNCLI', 'Data Source=<server name>;Integrated Security=SSPI').AdventureWorks2012.HumanResources.Section Gear up GroupName = 'Sales and Marketing' WHERE DepartmentID = 4;

Updating Large Object data types

Examples in this section demonstrate methods of updating values in columns that are defined with large object (LOB) information types.

R. Using UPDATE with .WRITE to change data in an nvarchar(max) column

The following instance uses the .WRITE clause to update a partial value in DocumentSummary, an nvarchar(max) column in the Production.Document table. The give-and-take components is replaced with the word features by specifying the replacement word, the starting location (offset) of the word to exist replaced in the existing data, and the number of characters to be replaced (length). The example likewise uses the OUTPUT clause to return the before and afterward images of the DocumentSummary column to the @MyTableVar table variable.

              USE AdventureWorks2012;   Get   DECLARE @MyTableVar TABLE (       SummaryBefore NVARCHAR(max),       SummaryAfter NVARCHAR(max));   UPDATE Production.Document   SET DocumentSummary .WRITE (N'features',28,10)   OUTPUT deleted.DocumentSummary,           inserted.DocumentSummary        INTO @MyTableVar   WHERE Title = N'Front Reflector Bracket Installation';   SELECT SummaryBefore, SummaryAfter    FROM @MyTableVar;   Go

S. Using UPDATE with .WRITE to add and remove data in an nvarchar(max) column

The following examples add and remove information from an nvarchar(max) column that has a value currently ready to NULL. Because the .WRITE clause cannot be used to modify a Cypher column, the column is first populated with temporary data. This data is then replaced with the correct information by using the .WRITE clause. The additional examples append information to the end of the column value, remove (truncate) information from the column and, finally, remove partial information from the cavalcade. The SELECT statements brandish the data modification generated past each UPDATE statement.

              USE AdventureWorks2012;   Get   -- Replacing NULL value with temporary data.   UPDATE Product.Certificate   SET DocumentSummary = Northward'Replacing Cypher value'   WHERE Title = N'Creepo Arm and Tire Maintenance';   GO   SELECT DocumentSummary    FROM Product.Document   WHERE Title = N'Creepo Arm and Tire Maintenance';   GO   -- Replacing temporary information with the right data. Setting @Length to NULL    -- truncates all existing data from the @Start position.   UPDATE Production.Document   Prepare DocumentSummary .WRITE(Northward'Carefully inspect and maintain the tires and crank arms.',0,Aught)   WHERE Title = N'Crank Arm and Tire Maintenance';   GO   SELECT DocumentSummary    FROM Product.Document   WHERE Title = N'Crank Arm and Tire Maintenance';   GO   -- Appending additional data to the end of the column by setting    -- @First to NULL.   UPDATE Production.Document   SET DocumentSummary .WRITE (North' Appending data to the end of the column.', NULL, 0)   WHERE Title = N'Crank Arm and Tire Maintenance';   GO   SELECT DocumentSummary    FROM Production.Document   WHERE Title = N'Creepo Arm and Tire Maintenance';   GO   -- Removing all data from @Showtime to the cease of the existing value by    -- setting expression to NULL.    UPDATE Production.Document   Set up DocumentSummary .WRITE (NULL, 56, 0)   WHERE Title = Due north'Crank Arm and Tire Maintenance';   GO   SELECT DocumentSummary    FROM Product.Document   WHERE Title = N'Crank Arm and Tire Maintenance';   GO   -- Removing partial information offset at position 9 and ending at    -- position 21.   UPDATE Production.Document   SET DocumentSummary .WRITE ('',9, 12)   WHERE Title = N'Crank Arm and Tire Maintenance';   Get   SELECT DocumentSummary    FROM Production.Certificate   WHERE Title = N'Creepo Arm and Tire Maintenance';   Go

T. Using UPDATE with OPENROWSET to change a varbinary(max) column

The following instance replaces an existing image stored in a varbinary(max) column with a new epitome. The OPENROWSET function is used with the Bulk option to load the paradigm into the column. This example assumes that a file named Tires.jpg exists in the specified file path.

              Utilize AdventureWorks2012;   GO   UPDATE Production.ProductPhoto   SET ThumbNailPhoto = (       SELECT *       FROM OPENROWSET(BULK 'c:Tires.jpg', SINGLE_BLOB) AS x )   WHERE ProductPhotoID = 1;   Become

U. Using UPDATE to alter FILESTREAM data

The post-obit case uses the UPDATE argument to modify the data in the file system file. We do not recommend this method for streaming large amounts of data to a file. Use the advisable Win32 interfaces. The following example replaces whatsoever text in the file record with the text Xray one. For more data, see FILESTREAM (SQL Server).

              UPDATE Archive.dbo.Records   SET [Chart] = CAST('Xray 1' as VARBINARY(max))   WHERE [SerialNumber] = 2;

Updating User-defined Types

The following examples modify values in CLR user-defined type (UDT) columns. Three methods are demonstrated. For more than information most user-defined columns, see CLR User-Defined Types.

Five. Using a system data type

You tin update a UDT by supplying a value in a SQL Server system information type, as long as the user-defined type supports implicit or explicit conversion from that type. The following example shows how to update a value in a column of user-defined type Point, by explicitly converting from a string.

              UPDATE dbo.Cities   SET Location = CONVERT(Point, '12.three:46.2')   WHERE Name = 'Anchorage';

W. Invoking a method

You tin can update a UDT by invoking a method, marked as a mutator, of the user-defined type, to perform the update. The following example invokes a mutator method of type Bespeak named SetXY. This updates the state of the instance of the type.

              UPDATE dbo.Cities   SET Location.SetXY(23.five, 23.5)   WHERE Name = 'Anchorage';

X. Modifying the value of a property or data member

You can update a UDT past modifying the value of a registered holding or public data member of the user-defined type. The expression supplying the value must be implicitly convertible to the type of the property. The following example modifies the value of belongings X of user-defined type Point.

              UPDATE dbo.Cities   SET Location.X = 23.5   WHERE Proper name = 'Anchorage';

Overriding the Default Behavior of the Query Optimizer by Using Hints

Examples in this section demonstrate how to use tabular array and query hints to temporarily override the default beliefs of the query optimizer when processing the UPDATE statement.

Circumspection

Considering the SQL Server query optimizer typically selects the best execution plan for a query, nosotros recommend that hints be used only every bit a last resort by experienced developers and database administrators.

Y. Specifying a tabular array hint

The following example specifies the table hint TABLOCK. This hint specifies that a shared lock is taken on the table Product.Product and held until the terminate of the UPDATE statement.

              USE AdventureWorks2012;   Go   UPDATE Production.Product   WITH (TABLOCK)   SET ListPrice = ListPrice * 1.10   WHERE ProductNumber Similar 'BK-%';   Become

Z. Specifying a query hint

The following example specifies the query hintOPTIMIZE FOR (@variable) in the UPDATE argument. This hint instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.

              USE AdventureWorks2012;   GO   CREATE Procedure Product.uspProductUpdate   @Production NVARCHAR(25)   AS   Set up NOCOUNT ON;   UPDATE Product.Product   Fix ListPrice = ListPrice * 1.ten   WHERE ProductNumber LIKE @Product   OPTION (OPTIMIZE FOR (@Product = 'BK-%') );   Go   -- Execute the stored procedure    EXEC Product.uspProductUpdate 'BK-%';

Capturing the results of the UPDATE statement

Examples in this section demonstrate how to utilize the OUTPUT Clause to return data from, or expressions based on, each row afflicted by an UPDATE statement. These results can be returned to the processing awarding for employ in such things as confirmation messages, archiving, and other such awarding requirements.

AA. Using UPDATE with the OUTPUT clause

The following example updates the column VacationHours in the Employee table by 25 pct for Employees with less than 10 VacationHours and also sets the value in the column ModifiedDate to the electric current appointment. The OUTPUT clause returns the value of VacationHours that exists before applying the UPDATE statement in the deleted.VacationHours column and the updated value in the inserted.VacationHours column to the @MyTableVar table variable.

Two SELECT statements follow that return the values in @MyTableVar and the results of the update performance in the Employee tabular array. For more than examples using the OUTPUT clause, come across OUTPUT Clause (Transact-SQL).

              USE AdventureWorks2012;   GO    --Brandish the initial data of the table to exist updated.   SELECT BusinessEntityID, VacationHours, ModifiedDate, HireDate   FROM HumanResources.Employee WHERE VacationHours < 10   GO    DECLARE @MyTableVar Table (       EmpID int NOT NULL,       OldVacationHours smallint,       NewVacationHours smallint,       ModifiedDate datetime);   UPDATE HumanResources.Employee   Set VacationHours =  VacationHours * 1.25,       ModifiedDate = GETDATE()    OUTPUT inserted.BusinessEntityID,         deleted.VacationHours,         inserted.VacationHours,         inserted.ModifiedDate   INTO @MyTableVar 	WHERE VacationHours < 10   --Display the event set of the table variable.   SELECT EmpID, OldVacationHours , NewVacationHours, ModifiedDate   FROM @MyTableVar;    GO   --Display the outcome set up of the table.   SELECT BusinessEntityID, VacationHours, ModifiedDate, HireDate   FROM HumanResources.Employee 	WHERE VacationHours < 10   GO

Using UPDATE in other statements

Examples in this department demonstrate how to utilise UPDATE in other statements.

AB. Using UPDATE in a stored procedure

The post-obit example uses an UPDATE statement in a stored process. The procedure takes i input parameter, @NewHours and ane output parameter @RowCount. The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. The @RowCount output parameter is used to return the number of rows affected to a local variable. The CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is gear up to the value specified in @NewHours.

              USE AdventureWorks2012;   Become   CREATE Procedure HumanResources.Update_VacationHours   @NewHours SMALLINT   Every bit    Set NOCOUNT ON;   UPDATE HumanResources.Employee   Ready VacationHours =        ( Example            WHEN SalariedFlag = 0 So VacationHours + @NewHours            ELSE @NewHours          END       )   WHERE CurrentFlag = 1;   GO      EXEC HumanResources.Update_VacationHours 40;

AC. Using UPDATE in a TRY...Catch Block

The post-obit example uses an UPDATE statement in a Effort...Catch cake to handle execution errors that may occur during the update operation.

              USE AdventureWorks2012;   Go   BEGIN TRANSACTION;      Begin TRY       -- Intentionally generate a constraint violation error.       UPDATE HumanResources.Section       SET Name = Due north'MyNewName'       WHERE DepartmentID BETWEEN 1 AND 2;   END TRY   BEGIN CATCH       SELECT             ERROR_NUMBER() AS ErrorNumber           ,ERROR_SEVERITY() AS ErrorSeverity           ,ERROR_STATE() Equally ErrorState           ,ERROR_PROCEDURE() As ErrorProcedure           ,ERROR_LINE() AS ErrorLine           ,ERROR_MESSAGE() AS ErrorMessage;          IF @@TRANCOUNT > 0           ROLLBACK TRANSACTION;   END Grab;      IF @@TRANCOUNT > 0       COMMIT TRANSACTION;   GO

Examples: Azure Synapse Analytics and Analytics Platform Arrangement (PDW)

AD. Using a uncomplicated UPDATE statement

The following examples show how all rows can be affected when a WHERE clause is non used to specify the row (or rows) to update.

This example updates the values in the EndDate and CurrentFlag columns for all rows in the DimEmployee table.

              -- Uses AdventureWorks      UPDATE DimEmployee   Set up EndDate = '2010-12-31', CurrentFlag='False';

You can also utilise computed values in an UPDATE statement. The following example doubles the value in the ListPrice cavalcade for all rows in the Product tabular array.

              -- Uses AdventureWorks      UPDATE DimEmployee   Ready BaseRate = BaseRate * 2;

AE. Using the UPDATE statement with a WHERE clause

The following instance uses the WHERE clause to specify which rows to update.

              -- Uses AdventureWorks      UPDATE DimEmployee   SET FirstName = 'Gail'   WHERE EmployeeKey = 500;

AF. Using the UPDATE statement with label

The post-obit example shows utilize of a LABEL for the UPDATE statement.

              -- Uses AdventureWorks      UPDATE DimProduct   Gear up ProductSubcategoryKey = 2    WHERE ProductKey = 313   Selection (LABEL = Due north'label1');

AG. Using the UPDATE statement with information from another table

This example creates a tabular array to shop total sales by year. It updates the total sales for the year 2004 by running a SELECT statement against the FactInternetSales table.

              -- Uses AdventureWorks      CREATE TABLE YearlyTotalSales (       YearlySalesAmount Coin Non Zippo,       Year SMALLINT NOT Zilch )   WITH ( DISTRIBUTION = REPLICATE );      INSERT INTO YearlyTotalSales VALUES (0, 2004);   INSERT INTO YearlyTotalSales VALUES (0, 2005);   INSERT INTO YearlyTotalSales VALUES (0, 2006);      UPDATE YearlyTotalSales   Gear up YearlySalesAmount=   (SELECT SUM(SalesAmount) FROM FactInternetSales WHERE OrderDateKey >=20040000 AND OrderDateKey < 20050000)   WHERE Year=2004;      SELECT * FROM YearlyTotalSales;

AH. ANSI join for update statements

This example shows how to update data based on the result from joining some other tabular array.

              CREATE TABLE dbo.Table1        (ColA INT NOT Zippo, ColB DECIMAL(ten,3) Non Cypher);   Become   CREATE TABLE dbo.Table2        (ColA INT NOT Zero, ColB DECIMAL(10,3) NOT Zilch);   Get   INSERT INTO dbo.Table1 VALUES(1, 10.0);   INSERT INTO dbo.Table2 VALUES(1, 0.0);   Become   UPDATE dbo.Table2    SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB   FROM dbo.Table2        INNER JOIN dbo.Table1        ON (dbo.Table2.ColA = dbo.Table1.ColA);   Get   SELECT ColA, ColB    FROM dbo.Table2; GO