How to update a table using multiple joins

Question

Post reply

How to update a table using multiple joins

vineet_

SSC-Addicted

Points: 475
More actions
December 9, 2009 at 4:27 pm

#207335

Update table1 SET
table1.Column_Name = table3.Column_Name
FROM table1
INNER JOIN table2
ON (
table1.column1 = table2.column1
table2.column2 = table2.column2
)
INNER JOIN table3
ON (
table3.column = table1.column
)
WHY IS MY QUERY FAILING?
It works if I have only two tables to join.
How to work with more than two tables ??
Thanks
------------------
Why not ?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

your joins look a little funky, and you are missing an AND in the join.

pseudo code sucks, because it hides real issues(table1,table2,etc...column1,column2, etc)

...you should show the real code you were trying; then we could provide you with a real, testable command you could test and rollback with until you are sure it is right.

this is not right:

...

INNER JOIN table2

ON (

table1.column1 = table2.column1

table2.column2 = table2.column2

)

best guess based on your pseudo code is:

UPDATE myAlias --an alias for the table you are joining

SET myAlias.Column_Name = table3.Column_Name

FROM table1 myAlias --set the alias

INNER JOIN table2

ON myAlias.column1 = table2.column1

AND myAlias.column2 = table2.column2

INNER JOIN table3

ON myAlias.column1 = table3.column1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vineet_ SSC-Addicted Points: 475 More actions · Answer 2

Yes you are right

I was missing the AND in my join...

working fine now.

Thanks

------------------
Why not ?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Vinay.Gambhir (12/9/2009)
Yes you are right
I was missing the AND in my join...
working fine now.
Thanks

Considering that you wrote the following...

table2.column2 = table2.column2

... I'd recommend you post your code and let's take a look-see. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)