May 8, 2012 at 12:45 pm
I have a table (sql 2000) that contains records for a manufacturing process. Each item produced is identified by a serial number and has 72 records that are physically realated to "positions". Numbered 1 - 72. For each serial number.
I just learned today that through a mis-communication we have been entering the data backwards the first entry should have been for position 72 and the last should have been position 1.
What is an efficient method of reversing the data in the table so that the information matches up with the correct position? Position 1 = 72, 2 = 71 .... .....72 = 1
May 8, 2012 at 12:47 pm
Would help to see what you are talking about. Please read the first article I reference below in my signature block. Following the instructions in that article will show you what and how to post the information needed to get the best answers possible to your questions.
May 8, 2012 at 1:31 pm
Can you just update your position column and set it to 73 minus what it is now?
May 8, 2012 at 1:39 pm
Here would be an example of the table. I reworded my originall post so that hopefully it is a bit clearer.
The table is fairly simple. Be a bit of a pain to construct the data manually, each produced item (thousands of them) has 72 records where the CblSn is the same, the 'Pos' field is numbered 1 - 72 to relate the rest of the data to a physical location. This relationship/location was entered backwards. The data that relates to Pos = 1 is actually the data for Pos =72. The date time value is all the same for each of the 72 related records. Thanks.
CREATE TABLE [dbo].[Cables] (
[CblSN] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[HphnSN] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pos] [int] ,
[GphnSN] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GPos] [int] NULL ,
[CBL_inst_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
May 8, 2012 at 1:43 pm
philgee (5/8/2012)
Here would be an example of the table. I reworded my originall post so that hopefully it is a bit clearer.The table is fairly simple. Be a bit of a pain to construct the data manually, each produced item (thousands of them) has 72 records where the CblSn is the same, the 'Pos' field is numbered 1 - 72 to relate the rest of the data to a physical location. This relationship/location was entered backwards. The data that relates to Pos = 1 is actually the data for Pos =72. The date time value is all the same for each of the 72 related records. Thanks.
CREATE TABLE [dbo].[Cables] (
[CblSN] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[HphnSN] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pos] [int] ,
[GphnSN] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GPos] [int] NULL ,
[CBL_inst_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
update dbo.Cables set
Pos = 73 - Pos;
The above assumes that all the data is incorrect in the Pos column for data currently in the table.
If not, you will need to establish the necessary criteria to restrict the update.
May 8, 2012 at 1:46 pm
The table is fairly simple. Be a bit of a pain to construct the data manually, each produced item (thousands of them) has 72 records where the CblSn is the same, the 'Pos' field is numbered 1 - 72 to relate the rest of the data to a physical location. This relationship/location was entered backwards. The data that relates to Pos = 1 is actually the data for Pos =72. The date time value is all the same for each of the 72 related records. Thanks.
Like the previous guy said, it looks like it could be as easy as:
UPDATE Cables
SET Pos = 73 - Pos
In other words, 72 becomes 1 (73-72), 71 becomes 2 (73-71) ... 1 becomes 72 (73-1)
May 8, 2012 at 1:51 pm
Will set up a test and see I was looking way too deep at the problem if this will get it. Will let you know thanks in advance.
May 8, 2012 at 1:58 pm
That was all that needs to be done after fixing the input. Thanks to all!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply