Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tryharder
Starting Member
17 Posts |
Posted - 2013-01-31 : 10:45:01
|
Hi,I’m new to SQL so please go easy on me!!I am after a coding that will allow me to take a set number of records based on a particular keycode For example, I have 10k records with a keycode of AX10. I now need to split the records coded AX10 in to two batched one of 6300 records and with a pack code of AX10002 and the remaining 3700 records with a pack code of AX10012. This new information needs to update in to a new field called for instance “pack code”Thanks in advance for you help |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-31 : 10:54:56
|
Do this in on a test server before you apply it to production. In the query below, you can leave the red KeyCode as it is if you just want any random 6300 records to get one pack code. But if you want to order it by some criteria, so that the first 6300 would be the first in that ordering scheme, change the red Keycode to whatever columns you want to order it by;WITH cte AS( SELECT ROW_NUMBER() OVER (ORDER BY KeyCode) AS NN, PackCode FROM TheTable WHERE KeyCode = 'AX10')UPDATE cte SET PackCode = CASE WHEN NN <= 3700 THEN 'AX10002' ELSE 'AX10012' END; |
|
|
tryharder
Starting Member
17 Posts |
Posted - 2013-01-31 : 11:01:00
|
Thanks James, will give it a try and let you know. Appreciate your speedy response. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-31 : 11:12:26
|
You are very welcome - glad to help. If you run into any issues, reply back. |
|
|
|
|
|