February 1, 2008 at 10:29 am
I have this in my table:
Table: detail_data
person_id, person_number, date, item, qty, visit_id, visit_number
1, A12, 1/1/08, AA, 5, 10, B11
1, A12, 1/1/08, AA, 1, 10, B11
1, A12, 1/1/08, AA, 3, 10, B11
The table does not have a primary key but I will need to add one with the following fields:
person_id, date, item, visit_id
If I attempt to add this now I will get a PK error as there are duplicates.
So I want to add a new field called time and populate this with a time starting at 12:00:00am and then increment a second to the time each time there is a duplicate record.
The table would then look like this:
Table: detail_data
person_id, person_number, date, time, item, qty, visit_id, visit_number
1, A12, 1/1/08, 12:00:00, AA, 5, 10, B11
1, A12, 1/1/08, 12:00:01, AA, 1, 10, B11
1, A12, 1/1/08, 12:00:02, AA, 3, 10, B11
Any ideas?
February 1, 2008 at 11:45 am
If I'm understanding you correctly, the best way would by to add an ID field and enable the identity property on it. This will automatically increment a unique number in this field whenever a new record is entered.
Strick
February 1, 2008 at 11:51 am
I'd go with the identity value or GUID. Not time.
February 1, 2008 at 12:16 pm
Hi Strick
Thanks for the reply. Unfortunately I am restricted to the fields currently defined in the table, I cannot add another field like the indentity. The time field does exist but is currently populated with nulls, and as I cannot change any of the other fields (as these are populated already with valid data), and this one is part of the key, it is the only one I can play with.
I plan on doing something like this
declare @update_time as smalldatetime
select @update_time = '12:00:00'
UPDATE detail_data
SET time = DATEADD('ss', 1, @update_time)
But I am unsure how to have it run through a loop for each person so that a second is incremented to each duplicate.
Thanks
February 1, 2008 at 12:27 pm
Hi,
I see,
Do you have the ability to change the default propert in your time field? You can set the default propert to (getdate()) which will timestamp this field as soon as a new record touches the table. That way you wont have to do anything extra to make your time unique. Time is always unique.
Strick
February 1, 2008 at 12:30 pm
It is a not a problem with new records being added - the load program has since been changed to accomodate that. It is only a problem with the records currently residing in the table that need the unique times updated, so we can move forward.
Thanks
February 1, 2008 at 12:31 pm
Bounce your records into a temporary table with an identity field on it.
Then do something similar to the pseudo query below
UPDATE DEST
SET DateTime = DATEADD(mm,T.ID-DT.FirstID,GETDATE())
FROM dbo.YourTable AS YT
INNER JOIN #TempTable AS T ON
YT. -- You will have to repeat this for all fields.
INNER JOIN (
SELECT <FieldList you want to be unique),MIN(ID AS FirstID)
FROM #TempTable
GROUP BY <FieldList you want to be unique)
) AS DT
ON T.
February 2, 2008 at 1:53 pm
David,
I tried this query
UPDATE detail_data1
SET time = DATEADD(ss,T.ID-DT.FirstID,GETDATE())
FROM detail_data1 AS YT
INNER JOIN detail_data1 AS T ON YT
INNER JOIN (
SELECT person_id, date, time, item, visit_id
FROM detail_data1
GROUP BY person_id, date, time, item, visit_id
) AS DT
ON T.
and received this error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'INNER'.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
What is wrong?
Thanks
February 2, 2008 at 10:29 pm
rolli (2/2/2008)
David,I tried this query
UPDATE detail_data1
SET time = DATEADD(ss,T.ID-DT.FirstID,GETDATE())
FROM detail_data1 AS YT
INNER JOIN detail_data1 AS T ON YT
INNER JOIN (
SELECT person_id, date, time, item, visit_id
FROM detail_data1
GROUP BY person_id, date, time, item, visit_id
) AS DT
ON T.
and received this error:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'INNER'.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
What is wrong?
Thanks
You're missing pieces to two parts of the statement.
UPDATE detail_data1
SET time = DATEADD(ss,T.ID-DT.FirstID,GETDATE())
FROM detail_data1 AS YT
INNER JOIN detail_data1 AS T
ON YT --<<<<<missing relation info, should be like "ON yt.id=t.id"
INNER JOIN (
SELECT person_id, date, time, item, visit_id
FROM detail_data1
GROUP BY person_id, date, time, item, visit_id
) AS DT
ON T --<<<<<missing relation info, should be like "ON t.id=dt.id"
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 3, 2008 at 2:24 am
This is a SQL Server 2005 forum, right?
To test, use this
-- Prepare sample data
DECLARE@Sample TABLE (Person_ID TINYINT, Person_Number CHAR(3), Date DATETIME, Item CHAR(2), Qty TINYINT, Visit_ID TINYINT, Visit_Number CHAR(3))
SET DATEFORMAT MDY
INSERT@Sample
SELECT1, 'A12', '1/1/08', 'AA', 5, 10, 'B11' UNION ALL
SELECT1, 'A12', '1/1/08', 'AA', 1, 10, 'B11' UNION ALL
SELECT1, 'A12', '1/1/08', 'AA', 3, 10, 'B11'
-- Show original data
SELECT*
FROM@Sample
-- Do the necessary update
UPDATEf
SETf.Date = DATEADD(SECOND, f.RecID, f.Date)
FROM(
SELECTDate,
ROW_NUMBER() OVER (PARTITION BY Person_ID, Date, Item, Visit_ID ORDER BY Qty) - 1 AS RecID
FROM@Sample
) AS f
-- Show updated data
SELECT*
FROM@Sample
And for your environment, use
UPDATEf
SETf.Date = DATEADD(SECOND, f.RecID, f.Date)
FROM(
SELECTDate,
ROW_NUMBER() OVER (PARTITION BY Person_ID, Date, Item, Visit_ID ORDER BY Qty) - 1 AS RecID
FROMDetail_Data
) AS f
N 56°04'39.16"
E 12°55'05.25"
February 3, 2008 at 2:32 am
stricknyn (2/1/2008)
You can set the default propert to (getdate()) which will timestamp this field as soon as a new record touches the table. That way you wont have to do anything extra to make your time unique. Time is always unique.
I have to disagree
-- Prepare sample data
DECLARE@Sample TABLE
(
MyColumn SMALLINT PRIMARY KEY CLUSTERED,
MyTime DATETIME DEFAULT (GETDATE())
)
-- Populate sample table
INSERT@Sample
(
MyColumn
)
SELECTNumber
FROMmaster..spt_values
WHEREType = 'p'
-- Show sample data
SELECT*
FROM@Sample
N 56°04'39.16"
E 12°55'05.25"
February 3, 2008 at 4:20 pm
Peso, you beat me to the punch on the ROW_NUMBER suggestion 🙂
If you're stuck with SQL 2000 then moving the data to a temp table with an identity and then moving it back is the best way to go.
However, using getDate() as the default isn't so bad. All of the inserted values will get the same date. Unless you're expecting duplicates within a single insert statement's data then you should be fine. In fact, it may help enforce uniqueness in the future 🙂
February 3, 2008 at 11:47 pm
I agree that the getdate() between single selects almost always are unique, but however you can't rely on that to 100%.
The getdate() function works very much like RAND(). It seeds when the statemen is executed and retains it's value through whole execution for that statement.
N 56°04'39.16"
E 12°55'05.25"
February 4, 2008 at 12:47 pm
Be very careful even with datetime for uniqueness. I believe the smallest time increment is 3ms. We've had this problem in the past several times. Even datetime keys can cause duplicates.
February 4, 2008 at 12:51 pm
Be very careful with datetime for uniqueness. I believe the smallest time increment is 3ms. We've had this problem in the past several times. Even datetime keys can cause duplicates.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply