HOWTO: Update Records in a Database Table With Data From Another Table (MS SQL Server)

Published 13 July 10 08:01 AM | Scott Mitchell

SQL's UPDATE statement makes it easy to update one or more records in a database table. The most common UPDATE statement pattern assigns static or parameterized values to one or more columns:

UPDATE MyTable SET
    Column1 = Value1,
    Column2 = Value2,
    ...
WHERE PrimaryKeyColumn = SomeValue

But what if you need to update values in one database table from values in another database table? There are a couple ways this can be done. One way is to use a subquery:

UPDATE MyTable SET
    Column1 = (SELECT SomeColumn FROM SomeOtherTable WHERE SuchAndSuch = ThisAndThat),
    Column2 = (SELECT SomeOtherColumn FROM SomeOtherTable WHERE SuchAndSuch = ThisAndThat),
    ...
WHERE PrimaryKeyColumn = SomeValue

Alternatively, you can use a JOIN statement in your UPDATE clause. The precise syntax seems to vary a bit based on the database system. For Microsoft SQL Server, use the following syntax:

UPDATE MyTable SET
    Column1 = SomeOtherTable.SomeColumn,   
    Column2 = SomeOtherTable.SomeOtherColumn,
    ...
FROM MyTable
    INNER JOIN SomeOtherTable ON
        MyTable.SuchAndSuch = SomeOtherTable.ThisAndThat
WHERE PrimaryKeyColumn = SomeValue

Bear in mind that updating data in one table with data from another table may be a sign of bad database design. I've seen developers use these approaches to duplicate data across multiple tables so that it's "easier" or "quicker" to write a query to read the data, but such actions are (usually) folly. Data duplication may make your SELECT queries easier to write, but you can very easily end up with data integrity issues.

So when might you need to copy data from one table to another? Consider a help desk application that allows support staff to create support tickets, where each ticket in the system exists as a record in the Tickets table. A ticket may have dozens of data points. To ease data entry, let's say that administrative users can create "ticket templates," which pre-define the values for a number of ticket data points that are stored in a table named TicketTemplates. Applying a template to an existing ticket would require that the existing record in the Tickets table have its non-set data points assigned to the corresponding default values of the selected template. Presume that the Tickets table has a TicketTemplateId field that indicates what template is to be applied to the ticket. Once this value has been assigned, the following UPDATE statement could be executed to copy the template's pre-defined values to the non-assigned data points in the Tickets table:

UPDATE Tickets SET
    PriorityId = ISNULL(Tickets.PriorityId, TicketTemplates.PriorityId),
    CategoryId = ISNULL(Tickets.CategoryId, TicketTemplates.CategoryId),
    IsPublic = ISNULL(Tickets.IsPublic, TicketTemplates.IsPublic),
    ...
FROM Tickets
    INNER JOIN TicketTemplates ON
        Tickets.TicketTemplateId = TicketTemplates.TicketTemplateId
WHERE Tickets.TicketId = @TicketId

Happy Programming!

Filed under: SQL

Comments

# Mike S. said on July 13, 2010 09:05 AM:

If you have multiple databases on the same SQL Server, then you can use this to do updates across databases. Simply refer to the tables as database.owner.table, and you can do cross database joins. I've used this to restore data that had been wiped out by mounting a backup and copying the deleted data back into the production database. (Not for the faint hearted.)

# Jay Patel said on July 13, 2010 07:10 PM:

I've used the third technique often when I need to import data from a temporary table into the primary table. Not an often discussed topic, so it's great that someone has posted it.

# Scott Reed said on August 25, 2010 04:57 AM:

I agree with Jay, I have ofthen for large daily SQL Agent processing tasks, I have had data pulled in to numerous tempory tables, then modifed as needs and the main table re-updated after joining the temporay and physical tables together. Handy technique :)

# Brent said on March 3, 2011 12:22 PM:

Using sqlserver and all tables being in the same database, I want to set table1.dept to table2.dept where table1.branch = table2.branch and where table1.division equals AAA or BBB.

Then I want to set table1.division to table2.division where table1.branch = table2.branch and where table1.division equals AAA or BBB. (I want to update only those records in table1 that have a division equal to AAA or BBB).

The end result will be table1.dept and table1.division being the same as table2.dept and table2.division

where table1.branch = table2.branch and where table1.division was AAA or BBB.

If it helps, I have a finite list of branches from another system that I used to update table2's dept and division. That same list matches the branches in table1.

Any hints?

Sorry if I didn't explain it well.

Thanks.

# biltu said on March 10, 2011 06:43 AM: