Developing Matt

My Technical Journal

update with subquery

with one comment

How many times will I have to google ‘how to update i with a subquery’ before I remember?  I don’t know how it happens, but every time I forget how to update a table with another source table’s information based on some matching field.  Updating a table is easy (‘update table set field = ‘bla bla’….) but if you want to update with another table’s data it quickly reveals that your understanding of the logic behind t-sql isn’t perfect.  Regardless, here is the script that will update ALL fields in your table from another source table.

update destinationtable
set destinationTable.destinationField = sourcetable.sourcefield
from destinationTable join sourcetable on destinationtable.CriteriaField = sourceTable.CriteriaField

Advertisements

Written by matt

July 17, 2007 at 9:46 am

Posted in T-Sql

One Response

Subscribe to comments with RSS.

  1. and inserts:

    insert into destinationtable
    select * from sourcetable
    where blabla = ‘bla’

    or

    insert into destinationtable from sourcetable where blabla = ‘bla’

    (of course, if the columns don’t match exactly you’ll want to specify like a normal insert statement)

    matt

    August 24, 2007 at 2:28 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: