update inner join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

update inner join

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-06-14 : 07:06:22

Hi,
As you can see the following two tables have different data.
So the IDs will vary.
Can you let me know the sql query to update table1
Thanks
-----------------
Table1

IndexID Name ParentIndexID
1 BR DATED NULL
2 BR IPE NULL
3 BR NYMEX NULL
4 DUBAI NULL
5 F10MEDCC NULL
6 F10MEDFC NULL
7 F10NWECC NULL
8 F10NWEFB NULL
9 F10NWEFC NULL
10 xx NULL
...
13 BR(1,1) NULL
...
20 WTI NYMEX Null
...
156 z Null
--------------------
Table2

table2ID table2Name Table2ParentIndexID
1 BR DATED 129
2 BR IPE 56
3 BR NYMEX 2
4 DUBAI 129
5 F10MEDCC 9
6 F10MEDFC 9
7 F10NWECC 9
8 F10NWEFB 9
9 F10NWEFC 13
...
13 F35NWEFB 129
...
56 WTI NYMEX NULL
...
129 BR(1,1) 131
...
131 xx NULL
------------------

The idea is to come up with an update query to update table1
to have this kind of data. Thanks

IndexID Name ParentIndexID
1 BR DATED 13
2 BR IPE 20
3 BR NYMEX 2
4 DUBAI 13
5 F10MEDCC 12
6 F10MEDFC 12
7 F10NWECC 12
8 F10NWEFB 12
12 F10NWEFC NULL
...
...
...

This is what i have so far:
update
Table1.ParentIndexID
set
Table1.ParentIndexID =
from
Table1
inner join Table2 on Table1.IndexID = Table2.table2ParentIndexID

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-14 : 08:18:27

[code]UPDATE T1
SET ParentIndexID = T2.table2ParentID
FROM Table1 T1
JOIN Table2 T2
ON T1.[Name] = T2.table2Name
AND table2ParentID IS NOT NULL[/code]
or

[code]UPDATE T1
SET ParentIndexID = T4.IndexID
FROM Table1 T1
JOIN Table2 T2
ON T1.[Name] = T2.table2Name
AND table2ParentID IS NOT NULL
JOIN Table2 T3
ON T2.table2ParentID = T3.table2ID
JOIN Table1 T4
ON T3.table2Name = T4.[Name][/code]

skn
Starting Member

1 Post

Posted - 2011-06-02 : 04:59:25

hai,

i have 3 tables names dbo.tblissue,dbo.tblissueDetails,dbo.tblcounterdetails

i want to retrieve counterid and stockid from the table dbo.tblcounterdetails,

then unit and Gwgt from table dbo.issuedetails,

with the condition tasktype=1,which is situated in the table dbo.tblissue

when i executed the query it will not take the value of task type.......

i think you get the relation between these tables can be identified from the code block.....

SELECT CD.CounterId AS COUNTER,
CD.StockId AS Stock,
ISNULL(SUM(TD.Unit),0) AS Unit,
ISNULL(SUM(TD.GWgt),0) AS Wgt,
I.TaskType
FROM tblCounterDetails CD
LEFT OUTER JOIN tblIssueDetails TD
ON TD.CNo = CD.CounterId
AND TD.ItemId = CD.ItemId
LEFT OUTER JOIN tblIssue I
ON I.Id = TD.IId
AND I.TaskType = 0
GROUP BY CD.CounterId,
CD.StockId,
I.TaskType

in this code the line

LEFT OUTER JOIN tblIssue I
ON I.Id = TD.IId
AND I.TaskType = 0

is not working...it may be the join problem with the third table.....will you please help me to make the code completely working?

thank you in advance..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources