October 7, 2016 at 5:04 pm
In a t-sql 2012 listed below, I want the lockids to be set individually from 117173 to 117678. The problem with the sql listed below is all the lockid values end of with the values of 117678. Thus can you show me the sql on how to get each row value set to a unique value?
declare @LoopCounter int = 117172
While (@LoopCounter <= 117678)
begin
set @LoopCounter = @LoopCounter + 1
update [dbo].[Locker]
set lockID=@LoopCounter
FROM .[dbo].[Locker]
where [schoolID] = 134
end
October 7, 2016 at 6:00 pm
wendy elizabeth (10/7/2016)
In a t-sql 2012 listed below, I want the lockids to be set individually from 117173 to 117678. The problem with the sql listed below is all the lockid values end of with the values of 117678. Thus can you show me the sql on how to get each row value set to a unique value?declare @LoopCounter int = 117172
While (@LoopCounter <= 117678)
begin
set @LoopCounter = @LoopCounter + 1
update [dbo].[Locker]
set lockID=@LoopCounter
FROM .[dbo].[Locker]
where [schoolID] = 134
end
Your loop is work just as designed. You are looping through the values 117172 to 117678 and on each iteration you are assign the lockID for all the lockers where the schoolID is 134.
There is some key information missing to assign a single id to a single locker. With no DDL or sample data it is difficult to provide much more guidance.
October 7, 2016 at 9:15 pm
Here are ddls of a few tables you wanted to see. I want each row in the locker table to have a unique value contained in the last 506 records in the lock table:
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[serialNumber] [varchar](20) NULL,
[type] [varchar](3) NULL,
[locationID] [int] NULL,
[grade] [varchar](4) NULL,
[reserved] [bit] NULL,
[lockID] [int] NULL,
[share] [bit] NULL,
[lockType] [varchar](3) NULL,
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
(
[lockerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_Lock] FOREIGN KEY([lockID])
REFERENCES [dbo].[Lock] ([lockID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_Lock]
GO
ALTER TABLE [dbo].[Locker] WITH CHECK ADD CONSTRAINT [FK_Locker_LockerLocation] FOREIGN KEY([locationID])
REFERENCES [dbo].[LockerLocation] ([locationID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_LockerLocation]
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_School]
GO
--------------
CREATE TABLE [dbo].[Lock](
[lockID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[serialNumber] [varchar](20) NULL,
[type] [varchar](3) NULL,
[comboSeq] [tinyint] NOT NULL,
CONSTRAINT [PK_Lock] PRIMARY KEY NONCLUSTERED
(
[lockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Lock] WITH NOCHECK ADD CONSTRAINT [FK_Lock_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Lock] CHECK CONSTRAINT [FK_Lock_School]
GO
-------------------
CREATE TABLE [dbo].[LockCombination](
[comboID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[lockID] [int] NOT NULL,
[seq] [tinyint] NOT NULL,
[combo] [varchar](8) NOT NULL,
CONSTRAINT [PK_LockCombination] PRIMARY KEY NONCLUSTERED
(
[comboID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
October 7, 2016 at 10:22 pm
Why not just use ROW_NUMBER() and then add a constant to it and do the whole update in one go? I don't think you need a cursor for this at all.
October 7, 2016 at 10:58 pm
wendy elizabeth (10/7/2016)
In a t-sql 2012 listed below, I want the lockids to be set individually from 117173 to 117678. The problem with the sql listed below is all the lockid values end of with the values of 117678. Thus can you show me the sql on how to get each row value set to a unique value?declare @LoopCounter int = 117172
While (@LoopCounter <= 117678)
begin
set @LoopCounter = @LoopCounter + 1
update [dbo].[Locker]
set lockID=@LoopCounter
FROM .[dbo].[Locker]
where [schoolID] = 134
end
with cte as
(
Select SchoolID, lockID,
Row_Number() Over(Partition by SchoolID order by LockerID) RowNum
From Locker
)
Update cte set
lockID = RowNum + 117172
where SchoolID = 134;
This is a way of doing this without a loop. It is untested air code.
You could also use a tally (or numbers) table or build an inline tally table in a cte.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 8, 2016 at 2:41 am
wendy elizabeth (10/7/2016)
In a t-sql 2012 listed below, I want the lockids to be set individually from 117173 to 117678. The problem with the sql listed below is all the lockid values end of with the values of 117678. Thus can you show me the sql on how to get each row value set to a unique value?declare @LoopCounter int = 117172
While (@LoopCounter <= 117678)
begin
set @LoopCounter = @LoopCounter + 1
update [dbo].[Locker]
set lockID=@LoopCounter
FROM .[dbo].[Locker]
where [schoolID] = 134
end
Quick question, why would you ever want to use a while loop for this?
😎
October 8, 2016 at 1:20 pm
I used this loop since I was trying to determine how to solve my problem.
October 8, 2016 at 2:36 pm
wendy elizabeth (10/8/2016)
I used this loop since I was trying to determine how to solve my problem.
Understood and appreciated. The question now, though, is... do you understand how the solution provided by "LinkUp" actually works so that you know how to do similar in the future?
--Jeff Moden
October 10, 2016 at 8:38 am
post deleted
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply