January 7, 2009 at 4:45 am
I have a table called CT_FS_Import_Revised that contains a TicketNumber and a TrackingNumber. In some cases, there can be multiple distinct TrackingNumbers for one distinct TicketNumber. Such a case would look like this:
TicketNumber TrackingNumber
88473 1Z9132330342455751
88473 1Z9132330342813944
In another table called ConsumableOrderDetail, I need to update TrackingNumber per the TicketNumber. The row count for a given TicketNumber will be the same between CT_FS_Import_Revised and ConsumableOrderDetail.
The Update statement I tired at first looks like this:
UPDATE dbo.ConsumableOrderDetail
SET TrackingNumber = tr.TrackingNumberRevised
FROM ConsumableOrderDetail cd
INNER JOIN ConsumableOrder co
ON cd.ConsumableOrderID = co.ConsumableOrderID
INNER JOIN (SELECT TicketIDRevised, TrackingNumberRevised, FileDateRevised FROM ##CT_FS_Import_Revised
WHERE NOT TrackingNumberRevised = '' AND NOT ASCII(TrackingNumberRevised) = 13) AS tr
ON co.TicketID = tr.TicketIDRevised
After the Update statement runs, the record in ConsumableOrderDetail for TicketNumber 88473 looks like this:
TicketNumber TrackingNumber
88473 1Z9132330342455751
88473 1Z9132330342455751
On thing to know is that in this case where there are multiple distinct TrackingNumbers per TicketID, the update of the TrackingNumbers is arbitrary.
In my second attempt, I tried it this way:
**************************
CREATE TABLE #TempConsumableOrderDetail
(TicketID int,
TrackingNumber varchar(50)
)
INSERT #TempConsumableOrderDetail
(TicketID)
select 85742
union all
select 88473
union all
select 88473
union all
select 89634
union all
select 89634
union all
select 89634
CREATE TABLE #CT_FS_IMPORT_Revised
(
TicketIDRevised Int NOT NULL,
TrackingNumberRevised varchar(50) NOT NULL,
FileDateRevised DateTime default GETDATE() NOT NULL
)
INSERT #CT_FS_IMPORT_Revised
(TicketIDRevised, TrackingNumberRevised)
select 85742, 'tn123'
union all
select 88473, 'br549'
union all
select 88473, 'b0425'
union all
select 89634, 'tn456'
union all
select 89634, 'tn789'
union all
select 89634, 'tn568'
--********
--SELECT * from #TempConsumableOrderDetail
--SELECT * from #CT_FS_IMPORT_Revised
DECLARE @TicketIDLocal INT, @TrackingNumberLocal NVARCHAR(100)
WHILE EXISTS(SELECT * FROM #CT_FS_Import_Revised)
BEGIN
SELECT TOP(1)
@TicketIDLocal = TicketIDRevised,
@TrackingNumberLocal = TrackingNumberRevised
FROM #CT_FS_Import_Revised
UPDATE #TempConsumableOrderDetail
SET TrackingNumber = @TrackingNumberLocal
WHERE TrackingNumber IS NULL AND TicketID = @TicketIDLocal
DELETE FROM #CT_FS_Import_Revised
WHERE TicketIDRevised = @TicketIDLocal AND TrackingNumberRevised = @TrackingNumberLocal
END
SELECT * from #TempConsumableOrderDetail
SELECT * from #CT_FS_IMPORT_Revised
drop table #TempConsumableOrderDetail
drop table #CT_FS_IMPORT_Revised
************************************
Basically, with this approach, I wound up with the same problem. I've also tried it with a cursor that incorporates ROW_NUMBER to create a surrogate key:
CREATE TABLE #TempConsumableOrderDetail
(TicketID int,
TrackingNumber varchar(50)
)
insert #TempConsumableOrderDetail
(TicketID)
select 85742
union all
select 88473
union all
select 88473
union all
select 89634
union all
select 89634
union all
select 89634
CREATE TABLE #CT_FS_IMPORT_Revised
(
TicketIDRevised Int NOT NULL,
TrackingNumberRevised varchar(50) NOT NULL,
TicketKey int NOT NULL,
FileDateRevised DateTime default GETDATE() NOT NULL
)
INSERT #CT_FS_IMPORT_Revised
(TicketIDRevised, TrackingNumberRevised, TicketKey)
select 85742,'tn123', 1
union all
select 88473,'br549', 1
union all
select 88473,'b0425', 2
union all
select 89634,'tn456', 1
union all
select 89634,'tn789', 2
union all
select 89634,'tn568', 3
--********
--SELECT * from #TempConsumableOrderDetail
--SELECT * from #CT_FS_IMPORT_Revised
--*************************************
DECLARE TicketCursor CURSOR
READ_ONLY
FOR SELECT
TicketID,
CAST(ROW_NUMBER() OVER(PARTITION BY TicketID ORDER BY TicketID)AS INT) AS TicketKey
FROM #TempConsumableOrderDetail
DECLARE @TicketID int, @TicketKey int, @i int
OPEN TicketCursor
FETCH NEXT FROM TicketCursor INTO @TicketID, @TicketKey
SET @i=0
WHILE EXISTS(SELECT * FROM #TempConsumableOrderDetail WHERE TicketID = @TicketID AND TrackingNumber IS NULL)
BEGIN
UPDATE #TempConsumableOrderDetail
SET TrackingNumber = ct.TrackingNumberRevised
FROM #CT_FS_Import_Revised ct
WHERE
ct.TicketKey=@i AND TicketID = @TicketID
SET @i=@TicketKey
FETCH NEXT FROM TicketCursor INTO @TicketID, @TicketKey
END
CLOSE TicketCursor
DEALLOCATE TicketCursor
*****************************
I think if I could get the Cursor correct, this would work(?).
What can I do to get the result I'm looking for?
Thank you for your help!
CSDunn
January 7, 2009 at 5:03 am
cdun2 (1/7/2009)
After the Update statement runs, the record in ConsumableOrderDetail for TicketNumber 88473 looks like this:TicketNumber TrackingNumber
88473 1Z9132330342455751
88473 1Z9132330342455751
What do you want it to look like?
What can I do to get the result I'm looking for?
Can't tell yet, because we don't know what the desired result is! At a guess, you only want to update one of those rows in ConsumableOrderDetail.
If you have multiple rows in ConsumableOrderDetail for each TicketNumber, how are they distinguished? How would you identify the row which you want to update?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 7, 2009 at 7:26 am
Thank you for your response. The row that gets updated is arbitrary as long as the distinct TrackingNumbers are paired up with the correct TicketID. In the example where I have a TicketID of 88473 and two distinct TrackingNumbers of 1Z9132330342455751 and 1Z9132330342813944, I should have the following result after the update:
TicketID TrackingNumber
88473 1Z9132330342455751
88473 1Z9132330342813944
Again, which row gets updated with which TrackingNumber does not matter as long as they are with the correct TicketID. The result I was getting on my first attempt at an update is this:
TicketID TrackingNumber
88473 1Z9132330342455751
88473 1Z9132330342455751
Each row per this TicketID is only being updated with one TrackingNumber. Does this help?
Thanks.
CSDunn
January 7, 2009 at 7:35 am
If it really doesn't matter which row gets updated, why keep more than one row?
If your rows might look like this after update TicketID TrackingNumber
88473 1Z9132330342455751
88473 1Z9132330342813944
... what did they look like before the update?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 7, 2009 at 8:10 am
... what did they look like before the update?
TrackingNumber in ConsumableOrderDetail is NULL before the Update. The table to be updated will always have an equivaltent number of rows for each TicketID per the number of distinct TrackingNumbers from #CT_FS_Import_Revised. There are other columns in ConsumableOrderDetail, but none of them are relevant to TrackingNumber.
January 7, 2009 at 8:30 am
cdun2 (1/7/2009)
... what did they look like before the update?
TrackingNumber in ConsumableOrderDetail is NULL before the Update. The table to be updated will always have an equivaltent number of rows for each TicketID per the number of distinct TrackingNumbers from #CT_FS_Import_Revised. There are other columns in ConsumableOrderDetail, but none of them are relevant to TrackingNumber.
So, before the update, rows in ConsumableOrderDetail might look like TicketID TrackingNumber
88473 null
88473 1Z9132330342813944
or like TicketID TrackingNumber
88473 null
88473 null but either way, it doesn't matter which row(s) get(s) updated because there's nothing else in the row which is relevant to tracking number. If this is the case then it would be logically valid to put the new tracking number against all rows for that TicketID - because only one TrackingNumber is now valid and insufficient data is recorded to accurately permit a row to be used as TrackingNumber history.
It sounds to me that TrackingNumber should be a property of order rather than orderdetail - but what about split delivery orders?
Why do you want to store the TrackingNumber in more than one place?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 7, 2009 at 8:36 am
[quote-0Why do you want to store the TrackingNumber in more than one place?[/quote-0]
They are actually only stored in ConsumableOrderDetail. The temp table that I am using receives data from an upstream process in an SSIS package.
It appears that the following Cursor will do the trick:
DECLARE @ticket_id int
, @tracking_number varchar(50)
DECLARE TicketCursor CURSOR FOR
SELECT TicketIDRevised
, TrackingNumberRevised
FROM #CT_FS_IMPORT_Revised
OPEN TicketCursor
FETCH NEXT FROM TicketCursor INTO @ticket_id, @tracking_number
WHILE @@Fetch_Status = 0
BEGIN
UPDATE x
SET trackingnumber = @tracking_number
FROM (
SELECT TOP 1
TicketID
, TrackingNumber
FROM #TempConsumableOrderDetail
WHERE TrackingNumber IS NULL
AND TicketID = @ticket_id
) x
FETCH NEXT FROM TicketCursor INTO @ticket_id, @tracking_number
END
CLOSE TicketCursor
DEALLOCATE TicketCursor
January 7, 2009 at 8:36 am
I suppose it is one of those cases of bad design with which you are stuck and can't change it... otherwise, if there is any possibility, I would suggest to re-think the requirements and how to implement them. I don't see any point in updating these two rows with numbers. These numbers are already stored in the database and can be accessed. Now you are trying to violate rules of normalization, because you will have the same number stored in 2 different places. You said that there is no difference which row will get which of the two... the rest of information does not relate to these numbers.
Fine, why do you want to put them there at all, if they are meaningless?
Well, and if you really can't help yourself, you'll have to differentiate somehow these 2 rows that you want to update. Does the table you are updateing have a primary key (unique index)? Or identity column? If not, then you'll have to create one of these, because there is no way to tell SQL Server to update only one of 2 identical rows. "Identical" from viewpoint of your update - in the meaning you are unable to define which is which in your update query. They may have some different values in some columns, but you don't know which columns it will be... so your query can't take them into account.
January 7, 2009 at 8:43 am
Only read your post after posting my reply. Yes, if the original situation is always NULL in all rows, then you can write update that will take the numbers one after another and update precisely 1 row WHERE column IS NULL.
It will cause nice mess if in some rows there already is some value in the column in the moment when update starts. If that's some staging table, with only new rows each time, then your solution will work. But I still have to say that I don't like it :-). There is some flaw in the design if you need to do this.
January 7, 2009 at 8:47 am
cdun2 (1/7/2009)
Why do you want to store the TrackingNumber in more than one place?
They are actually only stored in ConsumableOrderDetail. The temp table that I am using receives data from an upstream process in an SSIS package.
It appears that the following Cursor will do the trick:
You don't need the overhead or the statement complexity of a cursor to do this. If you absolutely must record the same piece of information kinda randomly within the database, we can do it with an UPDATE FROM similar to the first query in your first post. Just pick a column in ConsumableOrderDetail which changes with each row within the same TicketID.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply