StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTough SQL Update
text
Body
copied!<p>2 databases QF AND TK</p> <p>QF has the following:</p> <p>Imagine you have a table called FunctionalGroup with this data:</p> <pre><code>FunctionalGroupID | FunctionalGroup 1 Engineering 2 Purchasing </code></pre> <p>And a table that was a set of login's with a functionalgroupID to reference the group the person is in...</p> <pre><code>LoginID | FunctionalGroupID | Login 1 1 Jon 2 1 Joe 3 2 Jane </code></pre> <p>So Jon and Joe are engineering while Jane is purchasing..simple enough</p> <p>Now there is another database TK.</p> <p>TK has the following table Login with something to this effect:</p> <pre><code>Login | FunctionalGroupID Jon Purchasing Joe Purchasing Jane Purchasing </code></pre> <p>Notice how Jon and Joe in this database are now part of the purchasing group...But notice how this field is the text field and no ID. So what I want to do is use this table as the master data source and update the QF table such that the logins table from the QF now looks like this:</p> <pre><code>LoginID | FunctionalGroupID | Login 1 2 Jon 2 2 Joe 3 2 Jane </code></pre> <p>That is update this table to make Jon and Joe part of the purchasing group by setting their functionalgroupid = 2. Because 2 means purchasing. </p> <p>I tried this:</p> <pre><code>UPDATE Login SET Login.FunctionalGroupID = FunctionalGroup.FunctionalGroupID FROM Login INNER JOIN TKKCommonData.dbo.Login lz ON lz.Login = Login.Login AND lz.FunctionalGroupID = FunctionalGroup.FunctionalGroup </code></pre> <p>But I get an error:</p> <pre><code>Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "FunctionalGroup.FunctionalGroup" could not be bound. </code></pre> <p>This seems so easy but Im just not sure how to write the update statement. Im just looking to join the tables by the Login (which is the users name) and then by the Functionalgroup names.</p> <h3>I even tried this EDIT per Jay's answer with same error message</h3> <pre><code>UPDATE QuikFix.dbo.Login SET QuikFix.dbo.Login.FunctionalGroupID = QuikFix.dbo.FunctionalGroup.FunctionalGroupID FROM QuikFix.dbo.Login INNER JOIN TKKCommonData.dbo.Login ON TKKCommonData.dbo.Login.Login = QuikFix.dbo.Login.Login AND TKKCommonData.dbo.Login.FunctionalGroupID = QuikFix.dbo.FunctionalGroup.FunctionalGroup WHERE TKKCommonData.dbo.Login.LoginID= 101 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload