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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Help

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;
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -