Need to update all but first record in group by

I'd like to use an UPDATE statement to set 'Y' and 'N' values to a flag
field in a table called SVCrossSales for each set of records keyed by
the CSNum field. The first record in each group would get a CSFlg
value of 'N' and the subsequent records would get a 'Y'. Here's a
sample of what the CSFlg values should end up being for each CSNum:
INum CSNum CSFlg
-- -- --
2 1 N
2 1 Y
2 1 Y
2 2 N
2 2 Y
2 3 N
2 3 Y
2 3 Y
Instead of using a cursor to cycle through each CSNum, I'd like to use
an UPDATE statement with an aggregate join. I can't think of anything
else except using TOP to pick out the first record in the set.
Obviously, if I can set the 'N' values for CSFlg, I would do a simple
UPDATE to set the 'Y' values for the rest of the records.
This UPDATE fails:
UPDATE
SVCrossSales
SET
CSFlg = 'N'
FROM
SVCrossSales
INNER JOIN (
SELECT TOP 1 CSNum
FROM SVCrossSales
WHERE INum = 2
GROUP BY CSNum) GroupedSales ON (
SVCrossSales.CSNum = GroupedSales.CSNum)
WHERE
SVCrossSales.INum = 2
What is the proper syntax to do my UPDATE?>> I'd like to use an UPDATE statement to set 'Y' and 'N' values to a flag
Since there is no column values that can uniquely identify a row, this is
logically impossible.
Since a well designed table is a set of rows, the concept of
first/second/last etc. does not really apply. In a relational database,
you'd identify a row using the key values, not by positional descriptions.
TOP 1 without ORDER BY clause relies on certain internal materialization of
values, and it is much better to have
Once you have the keys & constraints explicitly declared, you can formulate
an UPDATE statement along the lines of:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales .INum
AND <predicate involving keys> )
AND INum = 2 ;
Anith|||Anith, I'm not sure how a WHERE EXISTS will help. It returns a boolean
result (true or false). How can that help identify the first record of
each set of CSNums? I need something to pick out the records marked
with an x below and set the CSFlg = 'N':
INum CSNum CSFlg
-- -- --
x 2 1 N
2 1 Y
2 1 Y
x 2 2 N
2 2 Y
x 2 3 N
2 3 Y
2 3 Y|||Steve,
I am not sure if you have taken time to read what I wrote. Note that it is
impossible to accomplish what you are asking to do, since there is no
logical way of identify a row without a key.
Also, you have not posted you table structures & sample data (
www.aspfaq.com/5006 ). It is not clear whether the sample data you posted
includes all the columns in the table or not. It is not clear whether you
have keys and constraints in your table.
The UPDATE statement I posted include a correlation denoted as <predicate
involving keys>. This is the most critical part is solving your problem. If
you have no keys, all bets are off.
Anith|||Sorry...should have posted this to begin with...
DDL for the SVCrossSales table:
CREATE TABLE [dbo].[SVCrossSales] (
[INum] [int] NOT NULL ,
[SalesNum] [int] NOT NULL ,
[CSNum] [int] NULL ,
[CSFlg] [char] (1) NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SVCrossSales] WITH NOCHECK ADD
CONSTRAINT [PK_SVCrossSales] PRIMARY KEY CLUSTERED
(
[INum],
[SalesNum]
) ON [PRIMARY]
GO
The INum is an ID field used in various tables of ours. The SalesNum
field is like an indentity seed in that it's a unique value for each
record in the table. Together, with INum, it forms the primary key for
the SVCrossSales table. I have a stored procedure that updates the
CSNum and then cycles through a cursor to update the CSFlg for each set
of recods with the same CSNum.
Sample data:
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
So, is there a way to use a single update to set the 'N' values?|||I should have also mentioned that the INum and SalesNum fields link to
a sales table (SVSales) where my stored procedure's logic figures out
which sales to group together and assign the next available CSNum to in
SVCrossSales. The last step in the sproc is to update the CSFlg.|||Based on the sample data you posted, if the CSFlag column already has the
values 'Y', then do:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE NOT EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum < SVCrossSales.SalesNum )
AND INum = 2 ;
If both the values 'Y' and 'N' are to be set, then do:
UPDATE SVCrossSales
SET CSFlg = CASE WHEN EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum <
SVCrossSales.SalesNum )
THEN 'Y' ELSE 'N' END
WHERE INum = 2 ;
You can re-write both these statements using a maxima function, but this
should give you what you are looking for.
Anith|||I replaced the cursor logic for updating CSFlg with the UPDATE
statement above. It didn't work. Also, it more than doubled the
execution time of the sproc. I don't understand what the EXISTS
subquery does. Shouldn't there be something to select the top 1 record
for each group set of records with the same CSNum?|||>> I replaced the cursor logic for updating CSFlg with the UPDATE statement
You will have to expand on that. Here is the data before & after update
based on the sample data you posted.
-- Before update
SELECT * FROM SVCrossSales ORDER BY INum, CSNum DESC, SalesNum
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 Y
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 Y
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 Y
2 336121 13255 Y
2 336122 13255 Y
-- Do the update
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE NOT EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum < SVCrossSales.SalesNum )
AND INum = 2 ;
-- After update
SELECT * FROM SVCrossSales ORDER BY INum, CSNum DESC, SalesNum
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
That might have something to do with the overall construction of the stored
procedure and suboptimal indexing.
Well, as I said, there are several ways you can derive the solution. In this
case, you can use TOP 1 as well like:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE SalesNum = ( SELECT TOP 1 s1.SalesNum
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
ORDER BY s1.SalesNum )
AND INum = 2 ;
Anith|||Hi There,
Let us See if this can help your cause
--
Update yourTable Set CSFlag = 'N' Where SalesNum In (Select
Min(SalesNum) From yourTable Group By INum,CSNum)
--
Please let me know if it worked for You.
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
With Warm regards
Jatinder Singh