Help joining data into table 1 from table 2 SQL

JOIN statement
I have 2 tables, table1 has columns A,B,C,D and table2 has columns B,C,D, I want to update table1 column A with table2 column B, where table1 c,d matches table2 b,c. I hope that was clear.

Software/Hardware used:
SQL server 2008

Answer Wiki

Thanks. We'll let you know when a new response is added.




FROM table1 alias1, table2 alias2



Discuss This Question: 7  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Sounds like a homework question. Can you give a real business application use for this question?
    124,235 pointsBadges:
  • JustAnotherITguy
    Its not homework, I have bcp exchange data into SQL table and now I have queried out by department all sent and received emails. I was trying to get the sent and received (2 different temp tables from my first query) data in the same row for a report.
    60 pointsBadges:
  • JustAnotherITguy
    This almost works, however I am getting a error on the Where clause that the "Multi-part identifier "table2.ColumnB" could not be bound"
    and same for table2.columnC
    60 pointsBadges:
  • JustAnotherITguy
    I figured it out, apparently the columns need to be in a FROM clause to identify the tables and then the ALIAS of each table if not used in the UPDATE statement, is assumed by SQL to be the left side.  So by adding FROM, defining the ALIAS and then Calling the ALIAS not the TABLE, it worked.

    60 pointsBadges:
  • jscott22

    all who suggest use of an alias value for each table are correct. however, as the two tables either have or do not have a direct multi-column level relationship, the most straight forward way to affect the desired update is as follows. no tricks involved here. it is just a basic SQL update query where the values to be used for update are selected from the update source to delivery to the table receiving the update.

    UPDATE table1 t1
    SET t1.ColA =(SELECT t2.ColB
    FROM table2 t2
    WHERE t2.ColB =t1.ColC AND t2.ColC = t1.ColD)

    25 pointsBadges:
  • JustAnotherITguy
    You can not add a ALIAS to the table in the UPDATE clause.. it must be in the From clause in SQL. if this was Oracle you would be right.


    SET T1.ColA = T2.ColB

    FROM table1 T1, table2 T2

    WHERE T1.ColC = T2.ColB AND T1.ColD = T2.ColC

    60 pointsBadges:
  • Brijesh

    Check the resource which guides you clearly with sufficient information Join data from two tables using SQL
    14,450 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: