UPDATE with multiple joins

Question

UPDATE with multiple joins

roy.tollison

SSCommitted

Points: 1772
More actions
November 8, 2013 at 11:12 am

#279005

and I can't seem to find the correct syntax.
UPDATE UPDTABLE
SET updtbl_lat = i.infotbl_lat,
updtbl_long = i.infotbl_long
FROM INFOTABLE i inner join DTLTABLE d on d.dtltbl_group = i.infotbl_group
inner join DTLTABLE e on e.dtltbl_code = updtbl_code
WHERE updtbl_number = i.infotbl_number
I get error that updtbl_number in the WHERE clause is an invalid column name...
the relationship is kind of a round robin thing. The UPDTABLE ties into the DTLTABLE through the _code column then
the columns from the UPDTABLE.updtbl_number and DTLTABLE.dtltbl_group tie into the INFOTABLE.infotbl_group and INFOTABLE.infotbl_number. Then i need the INFOTABLE columns _lat and _long to update to the UPDTABLE columns.
UPDTABLE
updtbl_lat
updtbl_long
updtbl_number
updtbl_code
INFOTABLE
infotbl_lat
infotbl_long
infotbl_group
infotbl_number
DTLTABLE
dtltbl_code
dtltbl_group

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

roy.tollison (11/8/2013)
and I can't seem to find the correct syntax.
UPDATE UPDTABLE
SET updtbl_lat = i.infotbl_lat,
updtbl_long = i.infotbl_long
FROM INFOTABLE i inner join DTLTABLE d on d.dtltbl_group = i.infotbl_group
inner join DTLTABLE e on e.dtltbl_code = updtbl_code
WHERE updtbl_number = i.infotbl_number
I get error that updtbl_number in the WHERE clause is an invalid column name...
the relationship is kind of a round robin thing. The UPDTABLE ties into the DTLTABLE through the _code column then
the columns from the UPDTABLE.updtbl_number and DTLTABLE.dtltbl_group tie into the INFOTABLE.infotbl_group and INFOTABLE.infotbl_number. Then i need the INFOTABLE columns _lat and _long to update to the UPDTABLE columns.
UPDTABLE
updtbl_lat
updtbl_long
updtbl_number
updtbl_code
INFOTABLE
infotbl_lat
infotbl_long
infotbl_group
infotbl_number
DTLTABLE
dtltbl_code
dtltbl_group

Pretty sparse on details here. You are trying to update the table UPDTABLE but that table is not in the list of tables. Without more detail than what you have posted I can't even begin to offer a correct syntax.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

roy.tollison SSCommitted Points: 1772 More actions · Answer 2

that's ok, i tinkered with it some more and managed to get what i needed.

Thank you,

Lowell SSC Guru Points: 323504 More actions · Answer 3

here's my best guess based on your post's info:

i'm explicitly aliasing the target table, so it might make it a little clearer:

UPDATE MyTarget

SET MyTarget.updtbl_lat = i.infotbl_lat,

MyTarget.updtbl_long = i.infotbl_long

FROM UPDTABLE MyTarget

INNER JOIN INFOTABLE i

ON MyTarget.updtbl_number = i.infotbl_number

INNER JOIN DTLTABLE d

ON d.dtltbl_group = i.infotbl_group

INNER JOIN DTLTABLE e

ON e.dtltbl_code = MyTarget.updtbl_code

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!

roy.tollison SSCommitted Points: 1772 More actions · Answer 4

roy.tollison

SSCommitted

Points: 1772

November 8, 2013 at 1:24 pm

#1665220

That is what i settled on.

Thank you.