updating records based on group by count 2

[Designware]

Hi,

I'm having difficulty selecting and dynamically updating rows in a single table, based upon the records having more than one shipto_id in any given order.

If I have data such as:

ORDER_NO SHIPTO_ID

1 20
1 21
1 21
2 22
2 22
2 23


I would like to update the shipto_id to whatever shipto_id that comes first. So the data would be changed to:

ORDER_NO SHIPTO_ID

1 20
1 20
1 20
2 22
2 22
2 22

I have a query that identifies all the unique order_no / shipto_id combinations, but only returns a single row for each unique combination.

SELECT ORDER_NO, SHIPTO_ID
FROM IMPL
WHERE ORDER_NO > 1
GROUP BY ORDER_NO, SHIPTO_ID
HAVING (COUNT (DISTINCT SHIPTO_ID)) > 1

If I declare a @variable in the script, how can I set / reset it to equal the first shipto_id that appears at the beginning of the group? Or are there other ways I can dynamically update the shipto_id appropriately?

Thanks!

 

[lameid]

This link should get you started...

http://www.akadia.com/services/sqlsrv_subqueries.html

Note that the update example is making a table and putting records in it for the purpose of demonstrating the scenario.

 

[imex]

Hi,

Try:

with CTE_Min as
(
    SELECT ORDER_NO, min(SHIPTO_ID) as SHIPTO_ID
    FROM IMPL
    WHERE ORDER_NO > 1
    GROUP BY ORDER_NO
)

update i
set SHIPTO_ID = m.SHIPTO_ID
from IMPL as i
inner join CTE_Min as m
    on m.ORDER_NO = i.ORDER_NO

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gmmastros]

I will show you a derived table technique for updating your data. I need to strongly caution you... the query I show below will update the data in the table. If the query is not right, that will not be my responsibility. I strongly encourage you to make a backup of your data prior to running this query just in case the results are not what you wanted.

To make this query work, we first must decide what "comes first" means. "Comes first" doesn't really have any meaning in SQL Server. Instead, we can use something a little more definitive, like the minimum value for each order number. We can easily get this minimum value with this query.

SELECT ORDER_NO, Min(SHIPTO_ID) As MIN_SHIPTO_ID
FROM IMPL
GROUP BY ORDER_NO

This code does not care if there are multiple rows or just a single row for each order number because the min of a group of 1 item will always be that item.

The trick now is to update the data in the real table based on the data that this query shows. This is where the derived table comes in.

Update IMPL
Set    SHIPTO_ID = Data.MIN_SHIPTO_ID
From   IMPL
       Inner Join (
         [blue]Select ORDER_NO, Min(SHIPTO_ID) As MIN_SHIPTO_ID
         From   IMPL
         Group BY ORDER_NO[/blue]
         ) As Data
         On IMPL.ORDER_NO = Data.ORDER_NO

Before you run this query, go backup your database. It's ok... I'll wait...

Now, notice the part in blue. That's the first query I wrote. It becomes a derived table by putting parenthesis around it and then giving it an alias. In this case, the alias is named "Data". Notice how I refer to the alias outside the parenthesis? That is an absolute requirement of using a derived table.

After you run the query, you should notice that the SHIPTO_ID is exactly the same for every order number, which I assume is what you wanted.

Also, please realize that there are other ways this query could have been written. I'm not necessarily saying this is the best method, but it is what I use whenever I need to do something like this.

If there is any part of this query that you don't understand, let me know and I will explain it.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Designware]

Thanks for the replies everyone.

Both examples worked perfectly ... and thanks for the link so I can learn and understand the functionality.

Dale