July 22, 2011 at 9:27 am
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.
July 22, 2011 at 10:09 am
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
July 22, 2011 at 11:05 am
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