August 25, 2009 at 4:00 am
I have this table
CREATE TABLE [Test] (
[Branch] [binary] (3) NOT NULL ,
[Order_YYYYMMDD] [binary] (8) NOT NULL ,
[delivery_YYYYMMDD] [binary] (8) NOT NULL ,
[delivery_HHMMSSHS] [binary] (8) NOT NULL ,
[OrderNo] [binary] (8) NULL ,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[Branch]
[Order_YYYYMMDD],
[delivery_YYYYMMDD],
[delivery_HHMMSSHS]
) ON [PRIMARY]
) ON [PRIMARY]
GO
by mistake the user enter two orders to be delievered at the same day and the same time
for examle
[Branch] [Order_YYYYMMDD][delivery_YYYYMMDD][delivery_HHMMSSHS]
11/1/20091/5/200912:10:23
12/1/20091/5/200912:10:23
I want to update [delivery_HHMMSSHS] such that no repeated times per day
any help please
August 25, 2009 at 4:18 am
Create a unique index on the column , and stop the issue happening in the first place
August 25, 2009 at 4:22 am
Dave Ballantyne (8/25/2009)
Create a unique index on the column , and stop the issue happening in the first place
how to update the old data such that no repeated time appear
August 25, 2009 at 4:22 am
Hold on , why have you used binary datatypes?
August 25, 2009 at 4:27 am
Dave Ballantyne (8/25/2009)
Hold on , why have you used binary datatypes?
it's a security issue , and I am not the resposable of the design
August 25, 2009 at 4:37 am
Go on then , im curious, how does it help security ?
August 25, 2009 at 4:57 am
Dave Ballantyne (8/25/2009)
Go on then , im curious, how does it help security ?
Security by obscurity.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2009 at 5:49 am
Hope there should of been a question mark after that Gail 🙂
Personally , I prefer security through security and use SQLServer's excellent inbuilt processes. 😉
August 25, 2009 at 6:05 am
ali.m.habib (8/25/2009)
Dave Ballantyne (8/25/2009)
Create a unique index on the column , and stop the issue happening in the first placehow to update the old data such that no repeated time appear
There are several methods. How do you wish to handle the issue? Do you plan to alter the data so that there are no duplicates? That seems problematic to me. Have you considered giving the table a unique key based on an identity column or similar? I'd like to help but you haven't really given us much to go on with yet.
I agree with Dave about the binary thing for what it's worth.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 25, 2009 at 6:10 am
If it is a one off then
make sure no one is able to update the table
Add an identity column
ALTER TABLE Test ADD TempID int IDENTITY(1,1)
Repeat the following update until no rows left to update (this will continue to add a second to the time to make it unique)
UPDATE t SET t.delivery_HHMMSSHS=CAST(CAST(REPLACE(CONVERT(char(8),DATEADD(second,1,CAST('1900-01-01 '+STUFF(STUFF(CAST(CAST(x.delivery_HHMMSSHS as int) as char(6)),5,0,':'),3,0,':') as datetime)),108),':','') as int) as binary(8))
FROM (SELECT a.delivery_YYYYMMDD,a.delivery_HHMMSSHS,MIN(a.TempID) AS [TempID]
FROM Test a
GROUP BY a.delivery_YYYYMMDD,a.delivery_HHMMSSHS
HAVING COUNT(*) > 1) x
INNER JOIN Test t ON t.delivery_YYYYMMDD=x.delivery_YYYYMMDD AND t.delivery_HHMMSSHS=x.delivery_HHMMSSHS
WHERE t.TempID >x.TempID
Drop the identity column
ALTER TABLE Test DROP COLUMN TempID
Add a constraint as David mentioned to prevent it occurring
*Edited for spelling mistake
Far away is close at hand in the images of elsewhere.
Anon.
August 25, 2009 at 6:31 am
Dave Ballantyne (8/25/2009)
Hope there should of been a question mark after that Gail 🙂
Perhaps, as I cannot state for certain, but that's what it looks like. At attempt to obscure the data so that if some does read it, it's difficult to understand
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply