November 2, 2011 at 9:29 am
Hi,
How do you update a group of records with the following "features",
By each group, update every record, with a counter which has to begin from 1 for each group.
I have build this solution, which does not satisfy all these request, how could I optimize ?:
USE B_D
GO
UPDATE a SET a.ORDER=1
FROM dbo.movimi As a
where substring(codle,1,3)='518' and
Codeba = 10491 and Form='F' and varname='03' and varyear=2011
I can't see a possible solution, please help.
Thanks
November 2, 2011 at 9:51 am
luismarinaray (11/2/2011)
Hi,How do you update a group of records with the following "features",
By each group, update every record, with a counter which has to begin from 1 for each group.
I have build this solution, which does not satisfy all these request, how could I optimize ?:
USE B_D
GO
UPDATE a SET a.ORDER=1
FROM dbo.movimi As a
where substring(codle,1,3)='518' and
Codeba = 10491 and Form='F' and varname='03' and varyear=2011
I can't see a possible solution, please help.
Thanks
Please provide some sample data, showing desired before and after status.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 2, 2011 at 9:56 am
ROW_NUMBER() partitioned by your group will give you the results that you want. If you want tested code, provide the sample data and results that Phil asked for.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 2, 2011 at 10:11 am
I hope this helps,
CREATE TABLE [dbo].[MOVIMI](
[Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[Codeba] [varchar](5) NULL,
[Order] [int] NULL CONSTRAINT [DF_MOVIMI_Order] DEFAULT ((0)),
[Form] [varchar](1) NULL,
[Codle] [varchar](10) NULL,
[Descrip] [varchar](125) NULL,
[varname] [nvarchar](2) NULL,
[varyear] [nvarchar](4) NULL
) ON [PRIMARY]
Data Before update:
Codeba,Order,Form,Codle,Descrip,varname,varyear
10491,0,F,4411110000,data text a,03,2011
10491,1,F,5100000000,data text b,03,2011
10491,2,F,5110000000,data text c,03,2011
10499,0,F,4411110000,data text a,03,2011
10499,1,F,5100000000,data text b,03,2011
10499,5,F,5110000000,data text c,03,2011
Data as should be:
10491,1,F,4411110000,data text a,03,2011
10491,2,F,5100000000,data text b,03,2011
10491,3,F,5110000000,data text c,03,2011
10499,1,F,4411110000,data text a,03,2011
10499,2,F,5100000000,data text b,03,2011
10499,3,F,5110000000,data text c,03,2011
Thanks a lot
November 2, 2011 at 11:31 am
Since windowed functions (like Row_Number) can't be used directly in an UPDATE statement, you'll need to use a CTE. Otherwise, you have everything that you need to solve this problem yourself. What have you tried?
I also suggest that you read the link in Phil's signature. The data you posted isn't readily consumable, so people are not as likely to help you. For example, you're missing INSERT statements and your strings aren't quoted.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply