Back To Basics: The UPDATE Statement

Published On: 2008-03-31By:

After you’ve inserted the data into the table, it’s time to update the data.  We do this by using the UPDATE statement.  The update statment can be used in two ways.  The first is to update a record or set of records in a single table, by simply filtering the data in the table by using values in the table.

UPDATE TableName
SET Column1 = 'Value'
WHERE AnotherColumn = 'AnotherValue'

A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.

UPDATE TableName
SET Column2 = AnotherTable.Column3
FROM AnotherTable
WHERE TableName.Column1 = AnotherTable.Column1

We can add joins into this as well, so that we can update more than one column from different tables at the same time.

UPDATE TableName
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
FROM AnotherTable
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = ThirdTable.Column1

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.

Denny


Contact the Author | Contact DCAC

2 responses to “Back To Basics: The UPDATE Statement”

  1. SQLWayne says:

    I had an amazing experience a few months ago at work (I’m the SQL Server DBA). I blew the minds of my boss, who has taught SQL Server, and our GIS admin, when I told and showed them that you can update data from views. They both thought that views were read-only.

    A bit of a rude awakening for them, they did not know that permissions were equally important for views as for tables.

  2. DeloeranGuy says:

    I’m new to this forum. Aren’t two of the above SQL statements in error? Specifically:

    UPDATE TableName
    SET Column2 = AnotherTable.Column3
    FROM AnotherTable
    WHERE TableName.Column1 = TableName.Column1

    Shouldn’t the WHERE read:

    WHERE TableName.Column1 = AnotherTable.Column1

    ? Just curious…

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via