Updating a Table in Sql if You Dont Know the Key

This article will get you lot familiar with the SQL UPDATE syntax and demonstrate how this argument tin be used for modifying data using T-SQL. Data modification side of DML language in T-SQL includes three statements used for modifying data in SQL Server and those are: INSERT, UPDATE, and DELETE. The focus here will exist on the UPDATE statement explicitly.

SQL UPDATE syntax

And then, to start with the definition, the UPDATE argument changes existing data in a table or view in SQL Server. Beneath is the full syntax for SQL Server and Azure SQL Database:

1 2 3 four 5 6 7 8 9 ten 11 12 thirteen xiv 15 16 17 xviii nineteen 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

[ WITH < common_table_expression > [ . . . n ] ] UPDATE [ Summit ( expression ) [ Per centum ] ] { { table_alias | < object > | rowset_function_limited [ WITH ( < Table_Hint_Limited > [ . . . n ] ) ] } | @ table_variable } SET { column_name = { expression | DEFAULT | NULL } | { udt_column_name . { { property_name = expression | field_name = expression } | method_name ( statement [ , . . . n ] ) } } | column_name { . WRITE ( expression , @ Offset , @ Length ) } | @ variable = expression | @ variable = cavalcade = expression | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression | @ variable { += | -= | *= | /= | %= | &= | ^= | |= } expression | @ variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression } [ , . . . n ] [ < OUTPUT Clause > ] [ FROM { < table_source > } [ , . . . n ] ] [ WHERE { < search_condition > | { [ Current OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ] } } ] [ Choice ( < query_hint > [ , . . . n ] ) ] [ ; ] < object > :: = { [ server_name . database_name . schema_name . | database_name . [ schema_name ] . | schema_name . ] table_or_view_name }

Don't let the syntax scare you. We will exist looking at the UPDATE argument using the minimum required syntax. The basic SQL UPDATE syntax comes downwards to using keyword UPDATE followed by the name of our object (tabular array or table allonym) and the Ready column name equals to some values.

The FROM clause will come into play when we do joins and we can as well have a WHERE clause when we need to update only a portion of data in a table. It goes without saying that using a WHERE in a statement is always a adept idea or y'all might find yourself in a state of affairs of updating every single record in a table.

• For the syntax of Azure Synapse Analysis (formerly SQL Data Warehouse) and Parallel Data Warehouse, please refer to official MS documentation: UPDATE (Transact-SQL)

The official documentation is a treasure trove of the UPDATE statement that will have y'all about xl minutes to read merely has everything that you demand to know in 1 place.

Modifying data using the UPDATE statement

In this section, we'll be modifying data in tables from the AdventureWorks2014 sample database. Even so, before we start with a simple UPDATE statement, execute a quick-select from Product tabular array:

USE AdventureWorks2014 ; GO SELECT * FROM Production . Product p ;

The query returns all records from the Product table of items that are sold or used in the manufacturing of sold products. In this table, we have a cavalcade MakeFlag that can exist either 0 or 1 as shown below:

0 = Product is purchased

1 = Product is manufactured in-business firm

Simple UPDATE argument

Permit's now run a simple UPDATE statement post-obit the SQL UPDATE syntax described before:

UPDATE Product . Product SET Product . Product . MakeFlag = 1 WHERE Production . Production . ProductID = 4 ;

After the execution, i row afflicted message volition be returned indicating that the statement went through:

Before the query was executed, the Headset Ball Bearings product was purchased just now it's manufactured in-business firm:

Update multiple rows

Let's motility on and look at another example to meet how nosotros tin can update multiple rows at once. This time, we'll as well use expressions from SQL UPDATE syntax which is a actually handy manner of setting a column equals to itself AKA doing something to a cavalcade on itself.

Use the query below to see what we have in the Employee tabular array:

SELECT * FROM HumanResources . Employee e ORDER By e . VacationHours DESC ;

The Employee table has information such as salary, department, title, etc. merely we're interested in the number of bachelor vacation hours:

We have a lot of different departments and task titles in the sample database, so allow's run another query and filter out some of the results by saying fetch everything where the job title is eastward.1000. Quality Assurance Technician:

SELECT * FROM HumanResources . Employee e WHERE e . JobTitle = 'Quality Assurance Technician' ;

Hither we tin can see that we take iv people with this job title and their available vacation hours:

Let's just say that those guys accept been performing really great for the by three months and we want to advantage them by giving them a twenty% increase in vacation hours. To practise that, execute the query below:

UPDATE HumanResources . Employee SET HumanResources . Employee . VacationHours = HumanResources . Employee . VacationHours * one.2 WHERE HumanResources . Employee . JobTitle = 'Quality Assurance Technician' ;

We should run into a message that 4 rows are affected meaning records for those four people from the QA department have been updated:

Nosotros can cheque how this update reflected vacation hours by re-executing the quick-select from the Employee table. You'll notice that vacation hours are increased by 20 percent:

Now, there's another SQL UPDATE syntax that we can use to become the exact same result. Instead of saying cavalcade name = column name, we could apply the expression which looks a trivial improve, too more than meaningful for some, as shown beneath:

UPDATE HumanResources . Employee Ready HumanResources . Employee . VacationHours *= 1.2 WHERE HumanResources . Employee . JobTitle = 'Quality Assurance Technician' ;

This is just another mode to use the Ready statement from the SQL UPDATE syntax and specify the list of columns or variable names to be updated.

It goes without saying that you lot can perform arithmetic operators like add-on(+), subtraction(-), multiplication(*) and division(/) on all numeric operands involved. For example, if you would like to undo changes and take that xx percentage back from the previous example, well and then just execute the following:

UPDATE HumanResources . Employee Ready HumanResources . Employee . VacationHours /= ane.2 WHERE HumanResources . Employee . JobTitle = 'Quality Assurance Technician' ;

Update data using Join

After going through some basics, allow's come across SQL UPDATE syntax on how to practice updates based on joins. This tin be a really neat thing to do because a lot of times when updating information in a table we demand data from another table to make desitions on what you're updating.

This can be tricky at first compared to joining stuff in a SELECT statement and it's non e'er straightforward, merely once yous go familiar with the syntax it gets easier.

With that in mind, let'due south forget the existing data in our sample database for a moment and see how to do updates using a join equally simple as it gets. For this, we'll need two new tables. Paste the code from below in the query editor and striking Execute:

CREATE TABLE dbo . Banking concern ( ID INT NOT Aught , BankName NVARCHAR ( 255 ) Cipher , City NVARCHAR ( 255 ) NULL , SwiftCode NVARCHAR ( 255 ) Nil , CONSTRAINT PK_Bank PRIMARY Fundamental CLUSTERED ( ID ASC ) ) ; GO CREATE Table dbo . SwiftCode ( ID INT Not Nothing , BankID INT NOT NULL , Code NVARCHAR ( 11 ) Zippo , CONSTRAINT PK_SwiftCode PRIMARY Central CLUSTERED ( ID ASC ) ) ; GO

We just created two empty tables on the dbo schema; Bank and SwiftCode:

I entered just a few records in both tables and if we do a quick-select from both tables, here's how information technology looks:

As can exist seen above, nosotros're missing the SwiftCode data from the Banking concern table. We take this data within the SwiftCode table, nosotros just demand to join those two tables using BankID and update the Bank tabular array using the data from another table.

So, allow's expect at the SQL UPDATE syntax beneath for achieving this:

UPDATE b Set up b . SwiftCode = sc . Code FROM Banking concern b INNER JOIN SwiftCode sc ON sc . BankID = b . id ;

Once executed, y'all should see a message that 4 rows are affected by this action:

This means that we merely updated the table by using data from some other table. Cool, right? If we query the Banking concern table one more time, here'due south what we should accept:

When working on a more than complex query, the rule of pollex is to always write a SELECT statement first and to just join tables together. Why? Because when y'all apply an UPDATE, everything later on the FROM is the exact same. Furthermore, information technology's highly advisable to use aliases or otherwise things can get a trivial funky, especially on complex queries no matter how familiar yous're with the SQL UPDATE syntax.

If yous're new to SQL and don't quite empathize what an alias is, past definition, aliases are temporary names to objects, so they're easier to piece of work with when writing and reading code. I, personally, utilise ApexSQL Complete which does this for me by automatically creating aliases to SQL tables and views with it'south Auto-generate aliases feature.

Conclusion

The UPDATE argument is i of the three big statements of the DML side of the T-SQL language that deals with data modification. By the stop of reading this commodity, hopefully, you got familiar with the SQL UPDATE syntax; nosotros saw some nuts of the statement, and how we tin do an update based on joins which is a very popular and common thing to do.

I hope this article has been informative for you and I thank you for reading it.

• Author
• Contempo Posts

Bojan aka "Boksi", an AP graduate in Information technology Engineering science focused on Networks and electronic technology from the Copenhagen Schoolhouse of Design and Engineering science, is a software analyst with feel in quality assurance, software support, product evangelism, and user engagement.

He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, mistake treatment to index strategies, and operation monitoring.

Bojan works at ApexSQL in Nis, Serbia as an integral function of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand up-alone tools and integrations into Visual Studio, SSMS, and VSCode.

See more than about Bojan at LinkedIn

View all posts by Bojan Petrovic

ouelletteollare.blogspot.com

Source: https://www.sqlshack.com/sql-update-syntax-explained/