Help joining data into table 1 from table 2 SQL

Tags:
JOIN
SQL
table
update query
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
ASKED: November 1, 2013  8:35 PM
UPDATED: November 6, 2013  10:50 PM

Answer Wiki

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

UPDATE

ALIAS1

SET ALIAS1.ColA = ALIAS2.ColB

FROM table1 alias1, table2 alias2


WHERE

ALIAS1.ColC = ALIAS2.ColB AND ALIAS1.ColD = ALIAS.ColC

Discuss This Question: 6  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ToddN2000
    Sounds like a homework question. Can you give a real business application use for this question?
    6,360 pointsBadges:
    report
  • 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:
    report
  • 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:
    report
  • 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:
    report
  • 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:
    report
  • 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.

    UPDATE T1

    SET T1.ColA = T2.ColB

    FROM table1 T1, table2 T2

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



    60 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following