Update from a select statement

Posted on 2009-04-17
Last Modified: 2013-11-05
I don't know why but this always trips me up.  I need to update tableA using the data from tableB.  How do I do that?
update tableA set field1 = 

	(select a.field2

	from tableA a, tableB b

	where b.field5 = a.field2

	and b.field3 = 13)

Open in new window

Question by:scross1276
    LVL 41

    Accepted Solution

    Give this a try:
    update tableA 
    set field1 = a.field2
    from tableA a, tableb b
    where b.field5 = a.field2
    and b.field3 = 13

    Open in new window


    Author Comment

    Ugh, so simple!  Thanks, it worked great!!
    LVL 26

    Expert Comment

    by:Chris Luttrell
    ralmada's should work, this is just in ANSI standard SQL with the JOIN.  I like this syntax better because it clearly defines the join conditions from the filter conditions in the WHERE clause.
    update tableA 
    set field1 = a.field2
    from tableA a JOIN tableb b ON b.field5 = a.field2
    where b.field3 = 13

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now