May 1, 2005 at 5:08 am
Hi everyone!!!
Sorry if this might turn out easy, but i want to know if this can be done
I have two tables A, B. For each record in table A there can be 2 or more records in table B. these two are linked by primary key.
I want to loop thought table B to find out how many child records it contains for each record of table A. and if it exceeds two I update a flag in table A.
Can this be done without cursors?
Thanks,
Shahed
May 1, 2005 at 9:35 am
Obviously, I haven't tested this but it should be real close.
UPDATE A
SET Flag = 1
FROM A,
(--Derived table "bd" finds everything in B having a count of 2 or more)
SELECT primarykey
FROM B
GROUP BY primarykey
HAVING COUNT(*) >=2) bd -- End of derived table "bd"
WHERE A.primarykey = B.primarykey
A "Derived Table" (also called an "Inline View"), is nothing more than a SELECT wrapped in parenthesis, given a table alias ("bd" in this case), added to the FROM clause as if it were a table, and the results of the SELECT are used as if they came from a table.
In SQL Server 2005, you'll also be able to do this using a thing known as a "CTE" or "Common Table Expression". The big difference there is that, once defined, it can be used anyway within the same proc or script (within the same batch, I think) just by using it's name (can self-join, as well) as if it were a table.
Both methods can be memory intensive if the results are huge so you need to be a tiny bit aware of what the expected return is going to be, but, for the most part, can be used without much fear.
Since you wanted to do this without a cursor, you're probably already aware how slow and resource intensive cursors are so I won't stand on the soapbox about why you should never use a cursor. And, I haven't seen anything that can't be done without using one. I will admit that sometimes you need to resort to a WHILE loop (hated brother of the cursor).
As with anything else, proper indexing on the underlying tables can help a great deal.
I'll also remind you that anytime you have a join in an UPDATE, the target of the update must be included in the FROM clause and properly joined or you could end up with 1) unexpected results or 2) a pegged CPU depending on how the optimizer feels. We had one guy at work peg 4 CPUs on just such a large update. He was pretty well embarassed.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2005 at 11:42 pm
Thanx a bunch Jeff. It works like a dream, i just replaced your Where condition by
WHERE A.primarykey = BD.primarykey
Shahed
May 2, 2005 at 6:32 am
Dang... I missed that. Sorry Shahed and thanks for the feedback. Glad you got it working...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2005 at 3:49 pm
set nocount on
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_b_a]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo]. DROP CONSTRAINT FK_b_a
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[a]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].
GO
CREATE TABLE [dbo].[a] (
[a_id] [int] IDENTITY (1000, 1) NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo]. (
[b_id] [int] IDENTITY (2000, 1) NOT NULL ,
[a_id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[a] WITH NOCHECK ADD
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
([a_id])
ON [PRIMARY]
GO
ALTER TABLE [dbo]. WITH NOCHECK ADD
CONSTRAINT [PK_b] PRIMARY KEY CLUSTERED
([b_id])
ON [PRIMARY]
GO
ALTER TABLE [dbo]. ADD
CONSTRAINT [FK_b_a] FOREIGN KEY
([a_id]) REFERENCES [dbo].[a] ([a_id]) ON DELETE CASCADE ON UPDATE CASCADE
GO
Declare @id int
-- p1 0 children
Insert a (name,flag) values ('pname1','')
Select @id = scope_identity()
-- p2 1 child
Insert a (name,flag) values ('pname2','')
Select @id = scope_identity()
Insert b (a_id, name) Values (@id, 'cname1')
-- p3 2 children
Insert a (name,flag) values ('pname3','')
Select @id = scope_identity()
Insert b (a_id, name) Values (@id, 'cname1')
Insert b (a_id, name) Values (@id, 'cname2')
-- p4 3 children
Insert a (name,flag) values ('pname4','')
Select @id = scope_identity()
Insert b (a_id, name) Values (@id, 'cname1')
Insert b (a_id, name) Values (@id, 'cname2')
Insert b (a_id, name) Values (@id, 'cname3')
Update
a
Set
a.Flag = 'Y'
From
a ( nolock )
Inner Join
(
Select
b.a_id
From
a ( nolock )
Inner Join
b ( nolock )
on b.a_id = a.a_id
Group By
b.a_id
Having
count(*) > 2) b
on b.a_id = a.a_id
Select
*
From
a ( nolock )
Left Outer Join
b ( nolock )
on b.a_id = a.a_id
May 2, 2005 at 8:55 pm
>The proprietary syntax has unpredictable results.
Only in something other than the SQL it was written in. It is not upredictable in SQL Server.
>Notice that we did not use the proprietary UPDATE.. FROM syntax, but kept to Standard SQL
That's a bit like saying "The x^y key does not appear on standard calculators so you should not use it even if you have a calculator that has such a key."
>Let's get the actual counts, whcih will do us more good than a flag.
Not if the only question you want to answer is mark things having more than 1. Sometimes ya just gotta follow the spec or you end up on the street. Is that right? Of course not, but I'd rather give in to the spec than to be out on the street. Not everyone can write a book to make a living, ya know.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2005 at 10:27 pm
Ok, so you've shown what doesn't work... what, in your estimation, would work if you were given that particular problem (the Adam Mechanic one) with that particular data?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply