SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tryharder
Starting Member

United Kingdom
17 Posts

Posted - 01/31/2013 :  10:45:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 01/31/2013 :  10:54:56  Show Profile  Reply with Quote
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

United Kingdom
17 Posts

Posted - 01/31/2013 :  11:01:00  Show Profile  Reply with Quote
Thanks James, will give it a try and let you know. Appreciate your speedy response.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 01/31/2013 :  11:12:26  Show Profile  Reply with Quote
You are very welcome - glad to help. If you run into any issues, reply back.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000