December 13, 2010 at 8:41 am
I'm a little confused with some code which is updating records in a table. Wonder if anyone out there might have an idea of what may be going on....
I'm running a nightly job which updates (and only updates, no inserts) records in a particular table. Periodically (and I can't see any pattern to this) it will fail with the following error message:
"Violation of PRIMARY KEY constraint 'pkBookingActivity'. Cannot insert duplicate key in object 'dbo.BookingActivity'. [SQLSTATE 23000] (Error 2627)..."
The code that runs nightly is as follows:
UPDATE BookingActivity
SET FareTypeId = b.FareTypeId
, FareAmount = ft.FlatRate
,FundingSourceId1 = b.FundingSourceId1
FROM Booking b
LEFT JOIN BookingLegs bl on bl.bookingid = b.bookingid
LEFT JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
LEFT JOIN FareTypes ft on b.FareTypeID = ft.FareTypeID
WHERE b.LDate = Convert(Varchar(10),(GETDATE() ),112)
AND b.CCSecGroup = 'ACCESS'
AND ba.FareTypeID IS NOT NULL
(The bookingactivity table does have 3 PKs listed, Faretypeid being one of them.) There is another script which runs before this which updates the booking table so that the faretypeid is set properly. I just want the bookingactivity table faretypeid to match what is in the booking table. (which is what the code does just fine, until it fails out of nowhere).
What am I missing here? What should I be looking for?
Help!
December 13, 2010 at 8:48 am
If you get a PK error, then you have a PK update occurring.
What I would do is change this to a SELECT, and pull out the PK columns from the update into the column list of your SELECT into a temporary table. Then join this back to the table with the error, looking for duplicates.
December 13, 2010 at 9:10 am
Please forgive, I'm a bit of a novice at this...Is this kind of what you were talking about? (in a round about way?)
SELECT ba.FareTypeId
, ba.legid
, ba.PassType
, ba.SpaceType
INTO #temp
FROM Booking b
LEFT JOIN BookingLegs bl on bl.bookingid = b.bookingid
LEFT JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
LEFT JOIN FareTypes ft on b.FareTypeID = ft.FareTypeID
WHERE b.LDate = Convert(Varchar(10),(GETDATE()-3 ),112)
AND b.CCSecGroup = 'ACCESS'
AND ba.FareTypeID IS NOT NULL
ORDER BY LegId
SELECT ba.FareTypeId
, ba.legid
, ba.PassType
, ba.SpaceType
INTO #temp2
FROM Booking b
LEFT JOIN BookingLegs bl on bl.bookingid = b.bookingid
LEFT JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
LEFT JOIN FareTypes ft on b.FareTypeID = ft.FareTypeID
WHERE b.LDate = Convert(Varchar(10),(GETDATE()-3 ),112)
AND b.CCSecGroup = 'ACCESS'
AND ba.FareTypeID IS NOT NULL
ORDER BY LegId
select t.faretypeid
, t.legid
, t.passtype
, t.spacetype
, t2.faretypeid
, t2.legid
, t2.passtype
, t2.spacetype
FROM #temp t
left join #temp2 t2 on t.LegId = t2.LegId
drop table #temp, #temp2
I don't think i did it right, but, I'm thinking if i join the same table to itself like i did, i should get the same number of records than if i just ran the query once by itself (if there weren't actual duplicates), right? In this one, if i run the query singularly, i get 9600 records, but if i run the one above with the join i get 9604. But I can't tell which are the duplicates.
December 13, 2010 at 2:54 pm
What is the primary key on bookingactivity?
You can find duplicates by using a GROUP BY on the pK and a having count(*) > 1. What you first need to do is join your #temp table with BookingActivity on the PK. Then in the SELECT, get the PK back along with a count(*).
December 13, 2010 at 3:24 pm
Excellent Steve! Thanks so much! I did end up getting it. Turns out there's something odd going on in the software because it did indeed duplicate the legID (one of the primary keys) for one trip on this day. Now onto trackling how to modify my update so that it excludes these weird extras.
Thanks so much for your help on this!
December 14, 2010 at 2:04 pm
You are welcome, and glad it's working.
You were on a good track, and using temp tables to work with a subset of data for troubleshooting is something you can use in the future.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply