T-SQL problem. Is it possible to get the result that I am looking for?

Question

T-SQL problem. Is it possible to get the result that I am looking for?

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

hmbtx (3/27/2011)
Ron:
Yes, Craig's code satisfied my requirements?
If it is okay, I would like to add one additional question based on Craig's solution and that is how can I change the script so that it will correct the regsiter_count in A_Header.
Example: Update A_Header set A_Header.register_count = drv.line_count.
Thanks
Howard

Like so: 🙂

UPDATE a

SET

register_count = drv.line_count

FROM

A_Header AS a

JOIN

( SELECT ali.Activity_Code, COUNT(*) AS line_count

FROM A_Line AS ali

GROUP BY ali.Activity_code

) AS drv

ON a.activity_code = drv.activity_code

where

drv.line_count <> a.register_count

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 2

Craig:

Thanks. The script worked just fine.

Am I correct that your original script that listed the counts in error cannot also correct the count?

Put another way, is there a way to list the rows with an error count and also correct the error count in the same script?

Howard

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

hmbtx (3/27/2011)
Craig:
Thanks. The script worked just fine.
Am I correct that your original script that listed the counts in error cannot also correct the count?
Put another way, is there a way to list the rows with an error count and also correct the error count in the same script?
Howard

Look into the OUTPUT clause, it will show you how to get the results from what you actually modified in a single statement.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 4

Craig:

Thanks for everything. You have been a great help.

Howard

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 5

Craig Farrell (3/27/2011)
Look into the OUTPUT clause...

Isn't this an SQL Server 2000 post? 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 6

Jeff:

Yes, this is an SQL 2000 post.

I incorrectly posted it to the wrong forum.

I apologize for that.

Howard

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Jeff Moden (3/27/2011)
Craig Farrell (3/27/2011)
Look into the OUTPUT clause...
Isn't this an SQL Server 2000 post? 🙂

Daaammmiiittt... you're right, Jeff... and I pointed that out in the first place! D'oh.

OUTPUT not available, errrr... hmmm... no, not really. Do your select into a #tbl, then do your update... it's really the only way that doesn't open up other cans of worms.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 8

See? I do pay attention once in a while. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)