March 2, 2014 at 5:57 am
Hi All,
I'm trying to update the following table (SQL Server 2008 R2):
MerchantNoTerminalNoCounterNo
7000001370000014NULL
7000001370000015NULL
7000001370000016NULL
7000001370000013NULL
7000003270000032NULL
7000003270000031NULL
I need the CounterNo to be updated as following, against TerminalNo:
MerchantNoTerminalNoCounterNo
7000001370000014Counter 4
7000001370000015Counter 3
7000001370000016Counter 2
7000001370000013Counter 1
7000003270000032Counter 2
7000003270000031Counter 1
I'm trying to do this by the following method, and stuck how to assign the value to the Counter?
DECLARE @CounterNo INT
SET @CounterNo = 1
WHILE (@CounterNo <= 5)
BEGIN
UPDATE tblRPT_TXNDetails SET RFMTerminalDesc = 'Counter ' + @CounterNo
END
Go
Thanx in Advance
March 2, 2014 at 6:19 am
-- always check an update as the equivalent SELECT before running it.
-- you may wish to replace the ORDER BY to, say, TerminalNo
SELECT
MerchantNo,
TerminalNo,
CounterNo = ROW_NUMBER() OVER(PARTITION BY MerchantNo ORDER BY (SELECT NULL))
FROM tblRPT_TXNDetails;
-- when you're satisfied that CounterNo looks correct, convert the query into an UPDATE like this:
WITH Updater AS (
SELECT RFMTerminalDesc, CounterNo = ROW_NUMBER() OVER(PARTITION BY MerchantNo ORDER BY (SELECT NULL))
FROM tblRPT_TXNDetails)
UPDATE Updater SET RFMTerminalDesc = CounterNo
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 2, 2014 at 7:45 am
eligiable (3/2/2014)
Hi All,I'm trying to update the following table (SQL Server 2008 R2):
MerchantNoTerminalNoCounterNo
7000001370000014NULL
7000001370000015NULL
7000001370000016NULL
7000001370000013NULL
7000003270000032NULL
7000003270000031NULL
I need the CounterNo to be updated as following, against TerminalNo:
MerchantNoTerminalNoCounterNo
7000001370000014Counter 4
7000001370000015Counter 3
7000001370000016Counter 2
7000001370000013Counter 1
7000003270000032Counter 2
7000003270000031Counter 1
I'm trying to do this by the following method, and stuck how to assign the value to the Counter?
DECLARE @CounterNo INT
SET @CounterNo = 1
WHILE (@CounterNo <= 5)
BEGIN
UPDATE tblRPT_TXNDetails SET RFMTerminalDesc = 'Counter ' + @CounterNo
END
Go
Thanx in Advance
It looks to me like Merchant No and Terminal No might be the unique identifier. Assuming the table to be updated as Table1 and the source as Table2 use the following
UPDATE a SET a.counterno = b.counterno
FROM Table1 a INNER JOIN Table2 b ON
a.MerchantNo = b.MerchantNo
and a.TerminalNo = b.TerminalNo
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 2, 2014 at 9:37 am
@OP - What you can take from this is everyone's answer is showing you a better way of doing to UPDATE so you can AVOID the loop 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2014 at 9:59 am
Thanx ChrisM@home :-), that really helped, check out the following statement:
SELECT DISTINCT TerminalNo, MerchantNo
INTO #tblGen_TermCounter
FROM tblRPT_TXNDetails;
ALTER TABLE #tblGen_TermCounter ADD CounterDesc BIGINT;
WITH Updater AS (
SELECT CounterDesc, CounterNo = ROW_NUMBER() OVER(PARTITION BY MerchantNo ORDER BY (SELECT NULL))
FROM #tblGen_TermCounter
)
UPDATE Updater SET CounterDesc = CounterNo;
ALTER TABLE #tblGen_TermCounter ALTER COLUMN CounterDesc NVARCHAR(50);
UPDATE #tblGen_TermCounter SET CounterDesc = 'Counter ' + CounterDesc;
UPDATE tblRPT_TXNDetails SET RFMTerminalDesc = #tblGen_TermCounter.CounterDesc
FROM #tblGen_TermCounter
WHERE tblRPT_TXNDetails.MerchantNo = #tblGen_TermCounter.MerchantNo AND tblRPT_TXNDetails.TerminalNo = #tblGen_TermCounter.TerminalNo
I cannot update the tblRPT_TXNDetails table directly, coz it contains more than 1 million records, and when I try to execute the statement on tblRPT_TXNDetails, the ROW_NUMBER() returns the Row Number on which the record is available in the table.
I needed to apply the same Counter No, to each record having the same TerminalNo, therefore I need to 1st pull out the Distinct Records based on TerminalNo and then to update the tblRPT_TXNDetails table.
Thanx again :-), please tell me, if require improvement.
March 2, 2014 at 10:01 am
Perry Whittle Thanx for the help :-).
March 2, 2014 at 11:50 am
eligiable (3/2/2014)
Thanx ChrisM@home :-), that really helped, check out the following statement:
SELECT DISTINCT TerminalNo, MerchantNo
INTO #tblGen_TermCounter
FROM tblRPT_TXNDetails;
ALTER TABLE #tblGen_TermCounter ADD CounterDesc BIGINT;
WITH Updater AS (
SELECT CounterDesc, CounterNo = ROW_NUMBER() OVER(PARTITION BY MerchantNo ORDER BY (SELECT NULL))
FROM #tblGen_TermCounter
)
UPDATE Updater SET CounterDesc = CounterNo;
ALTER TABLE #tblGen_TermCounter ALTER COLUMN CounterDesc NVARCHAR(50);
UPDATE #tblGen_TermCounter SET CounterDesc = 'Counter ' + CounterDesc;
UPDATE tblRPT_TXNDetails SET RFMTerminalDesc = #tblGen_TermCounter.CounterDesc
FROM #tblGen_TermCounter
WHERE tblRPT_TXNDetails.MerchantNo = #tblGen_TermCounter.MerchantNo AND tblRPT_TXNDetails.TerminalNo = #tblGen_TermCounter.TerminalNo
I cannot update the tblRPT_TXNDetails table directly, coz it contains more than 1 million records, and when I try to execute the statement on tblRPT_TXNDetails, the ROW_NUMBER() returns the Row Number on which the record is available in the table.
I needed to apply the same Counter No, to each record having the same TerminalNo, therefore I need to 1st pull out the Distinct Records based on TerminalNo and then to update the tblRPT_TXNDetails table.
Thanx again :-), please tell me, if require improvement.
I guess I don't understand the need for the 2 column alterations.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2014 at 12:03 pm
Hi Jeff Moden
1st alteration will allow to store the Counter values in CounterDesc as an INT or BigINT depends on the records (I got 1 Million and increasing), so I declared BIGINT.
The 2nd Time I need to put a Phrase 'Counter' with the generated value in CounterDesc so I need to alter it to NVARCHAR.
The Updater is not allowing to store the value in NVARCHAR or VARCHAR, that's why I need to alter it again.
March 2, 2014 at 2:13 pm
eligiable (3/2/2014)
Hi Jeff Moden1st alteration will allow to store the Counter values in CounterDesc as an INT or BigINT depends on the records (I got 1 Million and increasing), so I declared BIGINT.
The 2nd Time I need to put a Phrase 'Counter' with the generated value in CounterDesc so I need to alter it to NVARCHAR.
The Updater is not allowing to store the value in NVARCHAR or VARCHAR, that's why I need to alter it again.
You're storing the word "Counter", a space, and the character equivalent of a number in the column. It doesn't need to be NVARCHAR... it a waste of bytes when read into memory and a wast of bytes on the disk.
I also don't know why you feel compelled to store the word "Counter" in every row of the table. It's a denormalization that costs one byte for each letter (7 bytes), a byte for the space, 1 to 10 bytes for the character representation of a number, and 2 bytes to store the length indicator behind the scenes not to mention losing the advantages of a fixed datatype to a variable length datatype and mixing data in a column. That's a total of 11 to 19 bytes of storage where 4 bytes to store an INT would have been just fine. If you really need to have the word "Counter" present at display time, then add it at display time instead of storing a million copies of the word in a million row table.
If you elect to continue what I believe to be an unnecessary consumption of memory and disk space, you still don't need to change datatypes in the column to do this. Such changes to a Temp Table are fully logged in the logfile of TempDB and that takes a lot of time, memory, and disk thrashing if the table is large enough. I strongly recommend that you learn how to use CAST and CONVERT instead.
In fact, as Chris did in his code, there is no need for any of what you have posted as code. There's not even the need for a temp table. All you have to do with his code is to figure out if you want to continue the mistake of storing the word "Counter" in every row and learn how to use CAST or CONVERT on his code to help you continue making that mistake.
For your convenience, here's Chris' code again with the addition of the word "Counter".
WITH Updater AS (
SELECT RFMTerminalDesc, CounterNo = ROW_NUMBER() OVER(PARTITION BY MerchantNo ORDER BY (SELECT NULL))
FROM tblRPT_TXNDetails)
UPDATE Updater SET RFMTerminalDesc = 'Counter ' +CAST(CounterNo AS VARCHAR(10))
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2014 at 2:41 pm
eligiable (3/2/2014)
Hi Jeff Moden1st alteration will allow to store the Counter values in CounterDesc as an INT or BigINT depends on the records (I got 1 Million and increasing), so I declared BIGINT.
ROW_NUMBER returns BIGINT. One million is comfortably held by INT.
The 2nd Time I need to put a Phrase 'Counter' with the generated value in CounterDesc so I need to alter it to NVARCHAR.
The expression 'Counter' is VARCHAR.
The Updater is not allowing to store the value in NVARCHAR or VARCHAR, that's why I need to alter it again.
Your code probably gets the job done but as Jeff has shown you and fully explained, it's waaaay overkill for what you want to do.
Since you want the same TerminalNo values per MerchantNo I think DENSE_RANK should serve the purpose:
-- always check an update as the equivalent SELECT before running it.
-- you may wish to replace the ORDER BY to, say, TerminalNo
SELECT
MerchantNo,
TerminalNo,
CounterNo = 'Counter ' + CAST(DENSE_RANK() OVER(PARTITION BY MerchantNo ORDER BY TerminalNo) AS VARCHAR(10))
FROM tblRPT_TXNDetails;
-- when you're satisfied that CounterNo looks correct, convert the query into an UPDATE like this:
WITH Updater AS (
SELECT
RFMTerminalDesc,
CounterNo = 'Counter ' + CAST(DENSE_RANK() OVER(PARTITION BY MerchantNo ORDER BY TerminalNo) AS VARCHAR(10))
FROM tblRPT_TXNDetails)
UPDATE Updater SET RFMTerminalDesc = CounterNo
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 3, 2014 at 12:32 am
Thank you Jeff Moden
The explanation really helped, no need to store the Counter phrase into the database, I can display it on run-time, I didn't really know about the:DENSE_RANK()as mentioned by ChrisM@home.
Thanx for the help, really appreciated.
March 3, 2014 at 12:37 am
Thank you ChrisM@home
I really need to update, the statement works perfectly.
Thanks to you all.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply