Update - Inner Join Query - 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

SQL Server Development (2000)

Update - Inner Join Query

Author

Topic

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-05-23 : 23:23:23

Hi,

I have a table "originaldata" with the below columns
Context,Action/Recommendation ,Why

Another table called "tblcontextaction" with the below columns
Context,ActionRecommendation ,Why

Now I need to update the Why of "currentdata" with the Why of
"originaldata".

The Context and ActionRecommendation of each of the two tables are the same.

I tried the below query to check if I am getting the correct records for updating.

Select t1.Context,t1.ActionRecommendation from tblcontextaction t1
inner join originaldata t2
on
t2.Context = t1.COntext
and t2.[Action/Recommendation] = t1.[ActionRecommendation]

I am not getting the desired results the resultset is going on and on.

Please help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 23:29:58

quote:
Now I need to update the Why of "currentdata" with the Why of
"originaldata".

currentdata or tblcontextaction ?

quote:
I am not getting the desired results the resultset is going on and on.

What do you mean going on and on ?

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-05-23 : 23:35:26

The select query is not working.

Select t1.Context,t1.ActionRecommendation from tblcontextaction t1
inner join originaldata t2
on
t2.Context = t1.COntext
and t2.[Action/Recommendation] = t1.[ActionRecommendation]

This query is going on and on

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-05-23 : 23:35:33

When you say the resultset is going on and on what exactly do you mean?

a) The query takes a long time to run?
Try filtering (where context = ????) and seeing if the matching records are correct this should return faster if you filtered to a small set of records.

b) Too many matching records
Verify that you are joining on the correct unique set of values or key.

Also

Are Context and [action/recommendation] a unique combination for each table or do they repeat?

How many records in each of your two tables?
select count(*) from originaldata
select count(*) from tblcontextaction

How many distinct context, [action/recommendation]?

select context, [action/recommendation],count(*)
from originaldata
group by context, [action/recommendation]

select context, [actionrecommendation],count(*)
from tblcontextaction
group by context, [actionrecommendation]

What does this return?

select count(*)
from tblcontextaction t1 inner join originaldata t2
on t1.context = t2.context and t1.actionrecommendation = t2.[action/recommendation]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources