Multiple ID's - 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

General SQL Server Forums

New to SQL Server Programming

Multiple ID's

Author

Topic

Nexcompac
Starting Member

4 Posts

Posted - 2007-04-19 : 01:16:34

I am very green when it comes to SQL so don't be to ruff..

I was told that the "tblCouncilMembers" has multiple ID's that match "tblEmailGroup.DONEID" expression in the SQL query that follows. Then asked "Why would this be an issue when looking at the big picture?"

I sometimes need things spelled out, sometimes I don't. So, before you give me the answer, can you help me by pointing me in the right direction? Then, if I am a NooB beyond belief, you can spell it out and then make fun of me. =)

UPDATE tblEmailGroup
SET MemberID =
(SELECT Member_ID
FROM tblCouncilMembers
WHERE tblEmailGroup.DONEID = (tblCouncilMembers.DONE_ID))

Edit --> fix grammer =(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 01:33:24

It will be more efficient doing a JOIN, rather than doing a correlated subquery as yuo do right now.

UPDATE eg
SET eg.MemberID = cm.Member_ID
FROM tblEmailGroup AS eg
INNER JOIN tblCouncilMembers AS cm ON cm.DoneID = eg.DoneID

Peter Larsson
Helsingborg, Sweden

Nexcompac
Starting Member

4 Posts

Posted - 2007-04-19 : 01:44:44

Peso,
I have seen your work around the forums and you are well versed, I am sure of this. I see how your sugestion is great, but did it answer my original question? As I mentioned before, I am new to this languge.
"Why would this be an issue when looking at the big picture?"

__________________________________
Helping students one step at a time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 01:49:08

Because a correlated subquery is evaluated and executed for each and every record in the table you want to update.
For few records, maybe less than 100, this might not be an issue. But for larger number of records, the speed difference is not negliable. Also an INNER JOIN occupies less resources.

Peter Larsson
Helsingborg, Sweden

Nexcompac
Starting Member

4 Posts

Posted - 2007-04-19 : 10:30:30

The subquery is returning more than one row. The goal is to update a MemberID to a new value, but there is a conflict and the subquery returns multiple values.

quote:
Originally posted by Peso

Because a correlated subquery is evaluated and executed for each and every record in the table you want to update.
For few records, maybe less than 100, this might not be an issue. But for larger number of records, the speed difference is not negliable. Also an INNER JOIN occupies less resources.

Peter Larsson
Helsingborg, Sweden

__________________________________
Helping students one step at a time.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-19 : 10:46:51

quote:
Originally posted by Peso

Because a correlated subquery is evaluated and executed for each and every record in the table you want to update.

Well, not necessarily. The optimizer will make an attempt to develop a more efficient plan.

e4 d5 xd5 Nf6

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-19 : 11:13:18

quote:
Originally posted by Peso

Because a correlated subquery is evaluated and executed for each and every record in the table you want to update.
For few records, maybe less than 100, this might not be an issue. But for larger number of records, the speed difference is not negliable. Also an INNER JOIN occupies less resources.

Peter Larsson
Helsingborg, Sweden

It is not necessarily true that SQL Server will evaluate and execute for each and every record in the table when you use a correlated subquery.

When I run the following code, the query plan for the update that I get is for a merge join. SQL Server is smart enough to turn it into a join if it sees that it can.

However, there is still a good reason to use a join. If the subquery does not return a matching row, you would be setting the value of tblEmailGroup.MemberID to NULL.

To demo what happens if there is no matching row, change the number of the last row loaded into table #t2 in the code below to 1000 instead of 10000. It will generate an error when it tries to update the row to NULL.


create table #t1 (n1 int not null primary key clustered)
create table #t2 (n2 int not null primary key clustered)

insert into #t1 select number from F_TABLE_NUMBER_RANGE(0,10000)
insert into #t2 select number from F_TABLE_NUMBER_RANGE(0,10000)

-- Change last statement to this to generate error
-- insert into #t2 select number from F_TABLE_NUMBER_RANGE(0,1000)


update #t1
set
	N1 =	(
		select
			n1 = 0-#t2.n2
		from
			#t2
		where
			#t2.n2 = #t1.n1
		)

drop table #t1
drop table #t2

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-19 : 11:21:38

Not for nothing, but do you use DB2 OS/390...because that's the ONLY way to do it...and then you have to correlate as well after the fact

What's your background?

And if getting the right answer doesn't answer your question, then what are you looking for?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Nexcompac
Starting Member

4 Posts

Posted - 2007-04-19 : 11:39:36

Good point and good question. Why am I not looking for the right answer?

Sometimes, for me, if I solve the problem without knowing what the issue was, doesn't help me. Plus, how do I know I solved the right problem?

Essentially, I am in a learning process. My background is IT Tech. I have only used SQL 9.x and currently working twards a new possition.

__________________________________
Helping students one step at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources