Why doesn't this work for new records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Why doesn't this work for new records?

Author

Topic

mmckimson
Starting Member

6 Posts

Posted - 2011-07-07 : 12:40:14

I have two tables that look as follows:

dim_Pricing_Chain:

Chain_ID Chain_Description Date_Updated

253EBD6A-3B94-4580-85A8-4CAFD83D7121 Best Western 2011-04-20 00:00:00.0000000
48DE87F5-0D19-4744-AD8D-637C4FD8E714 Comfort Inn 2011-04-20 00:00:00.0000000
4301BE23-7C2A-475F-A8CD-9F93C14CB435 Fairmont 2011-04-20 00:00:00.0000000
5AA98AFF-80B8-4A00-9F2D-5F149B14C06D Hampton Inn 2011-04-20 00:00:00.0000000
F7B57FC2-0C9A-45A0-8DE9-1A59454BBBF3 Kimpton 2011-04-20 00:00:00.0000000
79A7C6E6-B811-4C6E-9DB4-F38D0804699E Marriott 2011-04-20 00:00:00.0000000
9BDEA78E-5088-49E2-AD80-5B1C6E0C133C Other 2011-04-20 00:00:00.0000000
4C95E897-E99D-4EA2-90DE-84915C1CAC70 Red Lion 2011-04-20 00:00:00.0000000
6993F2FC-FF7A-4EAB-85D8-56780730205E Sheraton 2011-04-20 00:00:00.0000000
F54C6679-E863-4D86-9757-C24496E78F8E Super 8 2011-04-20 00:00:00.0000000
FF0F6B69-7522-4A9A-A8CA-44B85587E6F1 Westin 2011-04-20 00:00:00.0000000
7ECF7564-33C3-4CBB-A791-FF945F615982 Red Roof 2011-04-21 00:00:00.0000000
8D379C89-CA30-4848-9881-08276E46680B Magnuson 2011-05-04 00:00:00.0000000

chaintemp2:

chainname

Sheraton
Hampton Inn
Other
Westin
Red Lion
Kimpton
Best Western
Hilton
Super 8
Fairmont
Red Roof
Comfort Inn
Marriott

I have this code:

UPDATE A
SET A.Chain_Description = B.chainname
FROM dim_Pricing_Chain as A
	INNER JOIN chaintemp2 as B
		ON A.Chain_Description = B.chainname
IF @@ROWCOUNT = 0
BEGIN
    INSERT into dim_Pricing_Chain (Chain_Description)
        Select B.chainname from chaintemp2 as B
END

Based upon my data, I would expect this statement to insert one new record for Hilton from the ChainTemp2 table, but it doesn't. If anyone can tell me what I'm doing wrong, I'd appreciate it...

Mike

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-07 : 13:04:03

Because your rowcount is greater than 0

I think this is what you're after:

UPDATE	A
SET	A.Chain_Description = B.chainname
FROM	dim_Pricing_Chain as A
INNER JOIN
	chaintemp2 as B
ON	A.Chain_Description = B.chainname


INSERT into dim_Pricing_Chain (Chain_Description)
Select	B.chainname
from	chaintemp2 as B
LEFT OUTER JOIN
	dim_Pricing_Chain as a
On	A.Chain_Description = B.chainname
WHERE	A.Chain_Description IS NULL

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-07 : 13:04:06

The update is going to update all the rows in A where there's a matching row in B. That's 12 rows. So @@Rowcount will be 12.

Hence the IF won't return true (RowCount is not 0), hence the insert won't run. That's probably a good thing, because if the IF did run, it would insert every single row from chaintemp2 into the Pricing Chain table, hence creating duplicates of those 12 records, so Best Western, Comfort Inn, Fairmont, etc would all then appear in the dim_Pricing_Chain table twice (probably a bad thing)

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 13:04:48

The update statement does not materially change the data, but it still updates all the rows that match the join condition. And since there are matching rows, @@ROWCOUNT will not be zero. So the if condition will be false.

What is the purpose of the update statement? Can you describe the business logic you are trying to implement?

Not once, twice

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-07 : 13:08:29

quote:
Originally posted by sunitabeck

Not once, twice

3 replies in 45 seconds!

mmckimson
Starting Member

6 Posts

Posted - 2011-07-07 : 13:26:28

Bingo! Thanks Russell. As someone who's completely self-taught, I wish I would have found this forum a lot sooner!

quote:

Originally posted by russell

Because your rowcount is greater than 0

I think this is what you're after:

UPDATE	A
SET	A.Chain_Description = B.chainname
FROM	dim_Pricing_Chain as A
INNER JOIN
	chaintemp2 as B
ON	A.Chain_Description = B.chainname


INSERT into dim_Pricing_Chain (Chain_Description)
Select	B.chainname
from	chaintemp2 as B
LEFT OUTER JOIN
	dim_Pricing_Chain as a
On	A.Chain_Description = B.chainname
WHERE	A.Chain_Description IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources