Update with related tables

• rootfixxxer

  SSCrazy

  Points: 2918

  More actions

  July 22, 2011 at 9:27 am

  #149252

  Hello

  Need some help, i don't know if i can't do this beacuse it's friday or i just can't.

  But here it goes, i have 3 tables that are related, the data that fill the tables come from some text files, i don't know the order that the files will be loaded so i need to update one related field in the end.

  I have the top table that has the raw materials, then i have a second table that has the end products and finally the third table that has the items that each product have.

  In the process of loading the files i fill all the tables but i can't relate the raw materials table with the end products, so i have to build an update query that connects the end products with the items and the items with raw materials.

  I put here some code with temp tables with a sample of my problem:

  DROP TABLE #TempTable1

  DROP TABLE #TempTable2

  DROP TABLE #TempTable3

  CREATE TABLE #TempTable1(

  id BIGINT IDENTITY(1,1) PRIMARY KEY,

  Text1 NVARCHAR(50),

  Text2 NVARCHAR(50)

  )

  CREATE TABLE #TempTable2(

  id BIGINT IDENTITY(1,1) PRIMARY KEY,

  idTemp1Update BIGINT,

  )

  CREATE TABLE #TempTable3(

  id BIGINT IDENTITY(1,1) PRIMARY KEY,

  idTemp2 BIGINT,

  Text1 NVARCHAR(50),

  Text2 NVARCHAR(50)

  )

  INSERT INTO #TempTable1 (Text1,Text2) VALUES ('First1','First2')

  INSERT INTO #TempTable1 (Text1,Text2) VALUES ('Second1','Second2')

  INSERT INTO #TempTable2 (idTemp1Update) VALUES (NULL)

  INSERT INTO #TempTable2 (idTemp1Update) VALUES (NULL)

  INSERT INTO #TempTable3 (idTemp2,Text1,Text2) VALUES (1,'First1','First2')

  INSERT INTO #TempTable3 (idTemp2,Text1,Text2) VALUES (2,'Second1','Second2')

  SELECT * FROM #TempTable1

  SELECT * FROM #TempTable2

  SELECT * FROM #TempTable3

  SO i need to update the field idTemp1Update in #TempTable2, to do that i need to guarantee that

  #TempTable1 Text1 and Text2 are equal to #TempTable3 Text1 and Text2 AND

  #TempTable2 id is equal to #TempTable3 idTemp2. When this happens i update idTemp1Update with the id from the #TempTable1 table.

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  July 22, 2011 at 10:09 am

  #1357170

  Try this:

  UPDATE tt2

  SET tt2.idTemp1Update = tt1.id

  FROM #TempTable2 AS tt2

  INNER JOIN #TempTable3 AS tt3 ON tt2.id = tt3.id

  INNER JOIN #TempTable1 AS tt1 ON tt3.Text1 = tt1.Text1

  AND tt3.Text2 = tt1.Text2

• rootfixxxer

  SSCrazy

  Points: 2918

  More actions

  July 22, 2011 at 11:05 am

  #1357195

  Thanks

  I'll try this later, now i have to leave.