Update current record based on data in next record

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

SQL Server 2005 Forums

Transact-SQL (2005)

Update current record based on data in next record

Author

Topic

mmccardle
Starting Member

43 Posts

Posted - 2010-11-05 : 12:31:35

I know there's a way to do this, with cursors I think, but they have always confused me a little. What I need is to update data in one record based on data found in the next record. This is the situation I have, we have jobs, each job has multiple operations and each operation has a work center assigned to it. I want to set a value equal to .5 for each operation EXCEPT if the next operation for the same job has the SAME work center. This is the expected result:


Job  Operation  WC  Value
1    10         A   .5
1    20         B    0
1    30         B   .5
1    40         C   .5
2    10         A   .5
2    20         B   .5
2    30         C    0
2    40         C   .5

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-05 : 12:52:25

Show the DDL for the tables too please

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-11-05 : 12:59:53

hello
i would add an ID field
id Job Operation WC Value
1 1 10 A .5
2 1 20 B 0
3 1 30 B .5
4 1 40 C .5
5 2 10 A .5
6 2 20 B .5
7 2 30 C 0
8 2 40 C .5

--then step through each record
declare @count int,@ op1 varchar(2), @op1 varchar(2),
set @count =1
while @count <= (select max(id) from yourTable) begin
set @op1 = (select operation from yourTable where id =@count)
set @op2 = (select operation from yourTable where id =@count+1)
if @op1<>@op2 update yourtable set value=.5 where id= @count
if @op1=@op2 update yourtable set value=0 where id= @count
@count=@count+1

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-11-05 : 13:02:45

OOPS
should be if @op1<>@op2 update yourtable set value=.5 from yourtable where id= @count

should be if @op1=@op2 update yourtable set value=0 from yourtable where id= @count

mmccardle
Starting Member

43 Posts

Posted - 2010-11-05 : 14:07:39

I may have oversimplified the example, in our case the row ID is assigned by the ERP system and is the row-pointer, which is completely random for each record.

This is the create statement for that table.


USE [GVC_Pilot]
GO
/****** Object:  Table [dbo].[jobroute]    Script Date: 11/05/2010 13:06:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jobroute](
	[job] [dbo].[JobType] NOT NULL,
	[suffix] [dbo].[SuffixType] NOT NULL CONSTRAINT [DF_jobroute_suffix]  DEFAULT ((0)),
	[oper_num] [dbo].[OperNumType] NOT NULL CONSTRAINT [DF_jobroute_oper_num]  DEFAULT ((0)),
	[wc] [dbo].[WcType] NOT NULL,
	[setup_hrs_t] [dbo].[TotalHoursType] NULL CONSTRAINT [DF_jobroute_setup_hrs_t]  DEFAULT ((0)),
	[setup_cost_t] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_setup_cost_t]  DEFAULT ((0)),
	[complete] [dbo].[ListYesNoType] NULL CONSTRAINT [DF_jobroute_complete]  DEFAULT ((0)),
	[setup_hrs_v] [dbo].[TotalHoursType] NULL CONSTRAINT [DF_jobroute_setup_hrs_v]  DEFAULT ((0)),
	[wip_amt] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_wip_amt]  DEFAULT ((0)),
	[qty_scrapped] [dbo].[QtyUnitType] NULL CONSTRAINT [DF_jobroute_qty_scrapped]  DEFAULT ((0)),
	[qty_received] [dbo].[QtyUnitType] NULL CONSTRAINT [DF_jobroute_qty_received]  DEFAULT ((0)),
	[qty_moved] [dbo].[QtyUnitType] NULL CONSTRAINT [DF_jobroute_qty_moved]  DEFAULT ((0)),
	[qty_complete] [dbo].[QtyUnitType] NULL CONSTRAINT [DF_jobroute_qty_complete]  DEFAULT ((0)),
	[effect_date] [dbo].[DateType] NULL,
	[obs_date] [dbo].[DateType] NULL,
	[bflush_type] [dbo].[BflushTypeType] NULL CONSTRAINT [DF_jobroute_bflush_type]  DEFAULT ('N'),
	[run_basis_lbr] [dbo].[RunBasisLbrType] NULL CONSTRAINT [DF_jobroute_run_basis_lbr]  DEFAULT ('H'),
	[run_basis_mch] [dbo].[RunBasisMchType] NULL CONSTRAINT [DF_jobroute_run_basis_mch]  DEFAULT ('H'),
	[fixovhd_t_lbr] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_fixovhd_t_lbr]  DEFAULT ((0)),
	[fixovhd_t_mch] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_fixovhd_t_mch]  DEFAULT ((0)),
	[varovhd_t_lbr] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_varovhd_t_lbr]  DEFAULT ((0)),
	[varovhd_t_mch] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_varovhd_t_mch]  DEFAULT ((0)),
	[run_hrs_t_lbr] [dbo].[TotalHoursType] NULL CONSTRAINT [DF_jobroute_run_hrs_t_lbr]  DEFAULT ((0)),
	[run_hrs_t_mch] [dbo].[TotalHoursType] NULL CONSTRAINT [DF_jobroute_run_hrs_t_mch]  DEFAULT ((0)),
	[run_hrs_v_lbr] [dbo].[RunHoursType] NULL CONSTRAINT [DF_jobroute_run_hrs_v_lbr]  DEFAULT ((0)),
	[run_hrs_v_mch] [dbo].[RunHoursType] NULL CONSTRAINT [DF_jobroute_run_hrs_v_mch]  DEFAULT ((0)),
	[run_cost_t_lbr] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_run_cost_t_lbr]  DEFAULT ((0)),
	[cntrl_point] [dbo].[ListYesNoType] NULL CONSTRAINT [DF_jobroute_cntrl_point]  DEFAULT ((1)),
	[setup_rate] [dbo].[RunRateType] NOT NULL CONSTRAINT [DF_jobroute_setup_rate]  DEFAULT ((0)),
	[efficiency] [dbo].[EfficiencyType] NULL CONSTRAINT [DF_jobroute_efficiency]  DEFAULT ((100)),
	[fovhd_rate_mch] [dbo].[OverheadRateType] NULL CONSTRAINT [DF_jobroute_fovhd_rate_mch]  DEFAULT ((0)),
	[vovhd_rate_mch] [dbo].[OverheadRateType] NULL CONSTRAINT [DF_jobroute_vovhd_rate_mch]  DEFAULT ((0)),
	[run_rate_lbr] [dbo].[RunRateType] NOT NULL CONSTRAINT [DF_jobroute_run_rate_lbr]  DEFAULT ((0)),
	[varovhd_rate] [dbo].[OverheadRateType] NULL CONSTRAINT [DF_jobroute_varovhd_rate]  DEFAULT ((0)),
	[fixovhd_rate] [dbo].[OverheadRateType] NULL CONSTRAINT [DF_jobroute_fixovhd_rate]  DEFAULT ((0)),
	[wip_matl_amt] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_wip_matl_amt]  DEFAULT ((0)),
	[wip_lbr_amt] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_wip_lbr_amt]  DEFAULT ((0)),
	[wip_fovhd_amt] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_wip_fovhd_amt]  DEFAULT ((0)),
	[wip_vovhd_amt] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_wip_vovhd_amt]  DEFAULT ((0)),
	[wip_out_amt] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobroute_wip_out_amt]  DEFAULT ((0)),
	[NoteExistsFlag] [dbo].[FlagNyType] NOT NULL CONSTRAINT [DF_jobroute_NoteExistsFlag]  DEFAULT ((0)),
	[RecordDate] [dbo].[CurrentDateType] NOT NULL CONSTRAINT [DF_jobroute_RecordDate]  DEFAULT (getdate()),
	[RowPointer] [dbo].[RowPointerType] NOT NULL CONSTRAINT [DF_jobroute_RowPointer]  DEFAULT (newid()),
	[CreatedBy] [dbo].[UsernameType] NOT NULL CONSTRAINT [DF_jobroute_CreatedBy]  DEFAULT (suser_sname()),
	[UpdatedBy] [dbo].[UsernameType] NOT NULL CONSTRAINT [DF_jobroute_UpdatedBy]  DEFAULT (suser_sname()),
	[CreateDate] [dbo].[CurrentDateType] NOT NULL CONSTRAINT [DF_jobroute_CreateDate]  DEFAULT (getdate()),
	[InWorkflow] [dbo].[FlagNyType] NOT NULL CONSTRAINT [DF_jobroute_InWorkflow]  DEFAULT ((0)),
 CONSTRAINT [PK_jobroute] PRIMARY KEY CLUSTERED 
(
	[job] ASC,
	[suffix] ASC,
	[oper_num] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_jobroute_RowPointer] UNIQUE NONCLUSTERED 
(
	[RowPointer] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [GVC_Pilot]
GO
ALTER TABLE [dbo].[jobroute]  WITH NOCHECK ADD  CONSTRAINT [jobrouteFk1] FOREIGN KEY([job], [suffix])
REFERENCES [dbo].[job] ([job], [suffix])
GO
ALTER TABLE [dbo].[jobroute] CHECK CONSTRAINT [jobrouteFk1]
GO
ALTER TABLE [dbo].[jobroute]  WITH NOCHECK ADD  CONSTRAINT [CK_jobroute_bflush_type] CHECK  (([bflush_type]='N' OR ([bflush_type]='B' OR ([bflush_type]='C' OR [bflush_type]='L'))))
GO
ALTER TABLE [dbo].[jobroute] CHECK CONSTRAINT [CK_jobroute_bflush_type]
GO
ALTER TABLE [dbo].[jobroute]  WITH NOCHECK ADD  CONSTRAINT [CK_jobroute_cntrl_point] CHECK  (([cntrl_point]=(0) OR [cntrl_point]=(1)))
GO
ALTER TABLE [dbo].[jobroute] CHECK CONSTRAINT [CK_jobroute_cntrl_point]
GO
ALTER TABLE [dbo].[jobroute]  WITH NOCHECK ADD  CONSTRAINT [CK_jobroute_complete] CHECK  (([complete]=(0) OR [complete]=(1)))
GO
ALTER TABLE [dbo].[jobroute] CHECK CONSTRAINT [CK_jobroute_complete]
GO
ALTER TABLE [dbo].[jobroute]  WITH NOCHECK ADD  CONSTRAINT [CK_jobroute_efficiency] CHECK  (([efficiency]>(0)))
GO
ALTER TABLE [dbo].[jobroute] CHECK CONSTRAINT [CK_jobroute_efficiency]
GO
ALTER TABLE [dbo].[jobroute]  WITH NOCHECK ADD  CONSTRAINT [CK_jobroute_run_basis_lbr] CHECK  (([run_basis_lbr]='P' OR [run_basis_lbr]='H'))
GO
ALTER TABLE [dbo].[jobroute] CHECK CONSTRAINT [CK_jobroute_run_basis_lbr]
GO
ALTER TABLE [dbo].[jobroute]  WITH NOCHECK ADD  CONSTRAINT [CK_jobroute_run_basis_mch] CHECK  (([run_basis_mch]='P' OR [run_basis_mch]='H'))
GO
ALTER TABLE [dbo].[jobroute] CHECK CONSTRAINT [CK_jobroute_run_basis_mch]