title: SQL Complex update query
body: <p>I need to update a table column white the same data that currently exists in that column plus add data from one column from another table. The tables in question are in 3N.</p>
  
  <p>I am attempting to do a complex update by concanting data from one column in a principle table into a column in an existing table.</p>
  
  <pre><code>update Catelog.Component SET Name = p.Number 
            FROM Catelog.Part p                                JOIN 
                 Catelog.ComponentPart cp ON p.ID = cp.PartID  JOIN 
                 Catelog.Component c      ON cp.ComponentID = c.ID  
            where p.BrandID = 1003
            AND ct.Name='Door' + '|'+ Name;
  </code></pre>
  
  <p>If you'll notice the Name column that I am setting I am resetting the data that already exists in that column plus prefixing the PartNumber.</p>
  
  <p>Basically I need to prefix the existing data in the Name column with the part number from the part table.</p>
  
  <p>Right now SQL is giving me the ambiguous error.</p>
  
  <p>Plus, I do not think that the rows would be set correctly the way I've got this update structured.</p>
  
  <p>What is the best way to do joined updates like this?</p>