[RESOLVED] sql server - what is the equivalent select query to this update?

[MMock]

I am not updating a row in a table that I should be, so I'm trying to see what's wrong. I have an update statement that joins from other tables:

	begin tran
	UPDATE [Item_Detail]
	SET bFlaggedToPull =1 
	FROM carton_detail cd 
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	WHERE classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = color_id
	AND ch.carton_number = '004002339835' -- @carton_number
	rollback

If I run it and roll it back, it says 26 rows affected. But if I run this, which I think is the equivalent select but i guess not, it says 39 rows affected.

select * 
	FROM [Item_Detail]
	JOIN carton_detail cd ON classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = color_id
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	WHERE ch.carton_number = '004002339835'

Do you see what I am doing wrong? Is one of the 39 selected the one I want to update, but it's not included in the 26 updated?

[NeedSomeAnswers]

In your first statement you are selecting from carton_detail as your main table where as in your second statement you are selecting from Item_Detail as your main table and this is causing your discrepancy.

You second statement should read something like -

        
        SELECT * 
	FROM carton_detail
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	WHERE classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = color_id
	AND ch.carton_number = '004002339835'

You were updating Item_Detail in your first statement not selecting from it !

[techgnome]

waaaaaait a minute... what does Item_Detail have to do with any of the other tables? you're updating Item_Detail, but selecting from two other tables... and I don't see how any of them relate to the others... so doesn't that mean it's going to update EVERYTHING in Item_Detail? Maybe it's just me, but usually when I'm updating my table, my UPDATE clause and my FROM clause match.

-tg

[MMock]

You were updating Item_Detail in your first statement not selecting from it !

I know it's something like that, but I don't have it quite yet. In the query you wrote for me, classstyle and color_id are columns in Item_Detail so your SELECT needs a join to that table. If I write the query like this (it was actually what i had tried first when I was testing):

    SELECT * 
	FROM carton_detail cd
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	JOIN [tempsamplepull].[dbo].[Item_Detail] on classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = color_id
	WHERE ch.carton_number = '004002339835'

It is still selecting 39 rows, not 26. So something in the UPDATE's criteria is filtering out 13 rows.

Do you know of an alternative way to see what rows you'd be updating? Or the only way is to write it as a SELECT? (Which is usually straightforward, but not in this case, I guess).

[MMock]

tg - your post came in while I was replying to the other. Let me see...

[techgnome]

I know it's something like that, but I don't have it quite yet. In the query you wrote for me, classstyle and color_id are columns in Item_Detail so your SELECT needs a join to that table. If I write the query like this (it was actually what i had tried first when I was testing):

    SELECT * 
	FROM carton_detail cd
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	JOIN [tempsamplepull].[dbo].[Item_Detail] on classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = color_id
	WHERE ch.carton_number = '004002339835'

It is still selecting 39 rows, not 26. So something in the UPDATE's criteria is filtering out 13 rows.

Do you know of an alternative way to see what rows you'd be updating? Or the only way is to write it as a SELECT? (Which is usually straightforward, but not in this case, I guess).

It could be that there are 26 rows in Item_Detail... but 39 in the others... how many rows do you get with SELECT * FROM Item_Detail?



-tg

[MMock]

So I rewrote to this:

	begin tran
	UPDATE [tempsamplepull].[dbo].[Item_Detail]
	SET bFlaggedToPull =1 
	FROM [tempsamplepull].[dbo].[Item_Detail] 
	JOIN carton_detail cd ON classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = color_id
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	WHERE  ch.carton_number = '004002339835' -- @carton_number
	rollback

It is still 26 rows. (I will admit I am not an expert on update statements that involve other tables, but you think my first way is wrong?)

[MMock]

Sorry, still cross posting, hope this isn't confusing to the reader.

Item_Detail contains 1663 rows total.

[techgnome]

That's what I'd expect... I'm lazy so I would have aliased everything...

	UPDATE tID -- couldn't just call it ID
	SET bFlaggedToPull =1 
	FROM [tempsamplepull].[dbo].[Item_Detail] tID
	JOIN carton_detail cd ON tID.classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = tID.color_id 
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	WHERE  ch.carton_number = '004002339835' -- @carton_number

Ok, the original query makes "sense" now... I missed the join to the unaliased fields originally ... I always alias and then always include my aliases for all fields, that way six months form now when I (or someone else is looking at it) there is no question what table the fields belong to.

here's what I suspect.... Item_Details has 26 rows... it joins to carton_detail on the color_id and classicstyle... there are more than 26 rows in carton_detail, with the same color_id and classicstyle... so you now get 39 rows... but since you're updating Item_Details, there's still only 26 rows to be updated...

-tg

edit - cross-posting once again... just saw the row count.... hmmm... now I'm not so sure. Other than there's still only 26 rows to be updated vs 39 in one of the other joined tables causing a Cartesian effect of some kind.

[MMock]

Maybe I should explain these tables a bit? carton_header is a table that contains a carton number, and inside a carton we have apparel items, so one carton can contain a lot of items. They are classified by sku, so in a carton we might have qty 10 of sku 123, qty 5 of sku 456, etc. A sku uniquely identifies an item by class, style color and size. And that is the carton_detail table. item_detail is a table that has a row with a unique classstyle (they're concatenated in this table) and color, and a boolean (bFlaggedToPull) that I need to update when we get a carton containing the item. Does that make sense? I want to update a row in item_detail based on the carton that is being processed - does that carton contain the item (bFlaggedToPull = 1) or not?

[MMock]

tg - If I could just see the rows that the UPDATE query is updating, without actually updating - like with the begin-tran/rollback, but not just see a count of the rows but actually see the rows. That is the point of the SELECT but it's pointless if it's not equivalent.

[techgnome]

I get that... which is why I always start with the select... then build the update based on that... which is why usually my update table always ends up in the from clause...

If using this update:

UPDATE tID -- couldn't just call it ID
	SET bFlaggedToPull =1 
	FROM [tempsamplepull].[dbo].[Item_Detail] tID
	JOIN carton_detail cd ON tID.classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = tID.color_id 
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	WHERE  ch.carton_number = '004002339835' -- @carton_number

The equivalent select is this:

-- UPDATE tID -- couldn't just call it ID
-- 	SET bFlaggedToPull =1 
select tID.*
	FROM [tempsamplepull].[dbo].[Item_Detail] tID
	JOIN carton_detail cd ON tID.classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = tID.color_id 
	JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
	WHERE  ch.carton_number = '004002339835' -- @carton_number

But that might still return more rows than you update because of the join... but at least you'd be able to see the duplicated rows...

-tg

[MMock]

Oh, of course, duplicated rows - that's what I was missing! The update doesn't update a row more than once, but of course your select can select it more than once - thank you!!!

Now I still have to figure out, which is why I embarked on this in the first place, why the row was NOT updated. The query in inside a stored procedure, so something must've gone wrong, but at least I've verified my UPDATE is correct.

Can I just ask, because NSA in post #2 made me unsure about my UPDATE statement, do I need to specify item_detail twice or is this query ok? Isn't FROM item_detail implied? Because when I run it and rollback, it says 26 rows which is the same as the UPDATE query with item_detail in the FROM clause (the alias is not used here because I wanted to post it exactly as I have it in the sproc; just know that classstyle and color_id are in item_detail)

			UPDATE [Item_Detail]
			SET bFlaggedToPull =1 
			FROM carton_detail cd 
			JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
			WHERE classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = color_id
			AND ch.carton_number = '004002339835'

[techgnome]

Oh, of course, duplicated rows - that's what I was missing! The update doesn't update a row more than once, but of course your select can select it more than once - thank you!!!

Right! Which is what I was trying to point out originally. Glad we got there eventually.

Can I just ask, because NSA in post #2 made me unsure about my UPDATE statement, do I need to specify item_detail twice or is this query ok? Isn't FROM item_detail implied? Because when I run it and rollback, it says 26 rows which is the same as the UPDATE query with item_detail in the FROM clause (the alias is not used here because I wanted to post it exactly as I have it in the sproc; just know that classstyle and color_id are in item_detail)

			UPDATE [Item_Detail]
			SET bFlaggedToPull =1 
			FROM carton_detail cd 
			JOIN carton_header ch ON ch.id_carton_header = cd.id_carton_header
			WHERE classstyle = cast(cast(cd.id_class as varchar) + cast(cd.id_style as varchar) as int) AND cd.id_color = color_id
			AND ch.carton_number = '004002339835'

I guess it's implied... I typically try to not leave things implied, so I always explicitly include my update table at some point. But as I pointed out, I always also alias everything you can't alias a table in the UPDATE, it has to be done nearer to the FROM (or one of the joins). Plus I usually work my updates as selects first, then work my way back out... in your case it sounds like you had the update part, and was working back to the select.

-tg

[MMock]

Right! Which is what I was trying to point out originally. Glad we got there eventually.
-tg

Sometimes I need to be hit over the head with things.

Thanks to you and NeedSomeAnswers for the discussion. There is still some issue where users are running this app and the UPDATE statement doesn't seem to "work", yet when I run the app it does (this was the reason for my post in the first place), but we've had some things going on, like over the weekend a big database move - these tables I'm joining used to be on separate servers but now they've both been moved to a new server and the same one - this led to updates to app.config and the cllick-once nature of the application should've installed new code but we found in one of our apps the users had to explicitly run setup.exe from the network. I have asked the users to close out and reinstall this app to be 100% sure they and I are running the same executable. I can't believe this has sucked up some much of my day. It's something I was planning on checking this morning and being satisfied that it all looked good, but no, it is not looking good yet. I will close this but may post back how the bigger picture problem was really resolved...or open a new thread. Sorry, I don't usually ramble so much in my closing statement!

[szlamany]

Sorry to come into the thread late...

I agree with TG - I always work out a complicated UPDATE/FROM by writing the matching SELECT. It's the only way to get comfortable with your population.

Also - when it's really complicated I will create a TEMP TABLE - put the PK's into it that I want to update. I would obviously do a SELECT DISTINCT to put them in only once.

Then use this TEMP table as the FROM and JOIN in just the single UPDATE table.

If you are looking at ways to debug this in production you can create a DEBUG table of sorts - and put the PK's for UPDATE into that TABLE. The table would need a PK that was an identity value and a TransDate field for you to put GETDATE() values into.

When you come to look at this DEBUG table all the rows put in during a single SPROC run would have the same TransDate values - so you can see how many were updated and which ones.

Seems like that might be a start in debugging what's happening in production...

[NeedSomeAnswers]

classstyle and color_id are columns in Item_Detail so your SELECT needs a join to that table

Ah i missed that, but techgnome got you there in the end.

I agree with TG

Me to, the main point being i too always write the Select statement first and then base the update statement off that!

There is still some issue where users are running this app and the UPDATE statement doesn't seem to "work", yet when I run the app it does

Is your SQL in a Stored Procedure? or is it in your app ?

Could you have problems with User rights? to either SP's or tables e.t.c?

[MMock]

There is still some issue where users are running this app and the UPDATE statement doesn't seem to "work", yet when I run the app it does

Found out the users were scanning fed ex tracking numbers and I was scanning carton numbers. Either number can be passed to the stored procedure but the author called it @carton_number instead of a non-generic name. So I thought all I had to do was match @carton_number = carton_header.carton_number, but I also need "or @carton_number = carton_header.tracking number". There's something to be said for naming your variables accurately! (The c# code in the application is helpful in that it's just called value and doesn't try to pretend to know what was input).

Thanks for being curious enough to stick with me! Obviously more testing was in order, and by someone other than me as the myopic author of this code.