Update with related tables

  • 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.

  • 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

  • Thanks

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

Viewing 3 posts - 1 through 2 (of 2 total)

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