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
 Update first 3000 rows, then next 3000 and so on
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/08/2013 :  07:12:39  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
I'm looking for a SQL query that will allow me to update the top 3,000 records in a list that has 30,000 contacts.

Records 1 to 3,000 need to have these fields changed within my contact table:

'fromname' = Johnny
'fromaddress' = Johnny@test.com
'replyaddress'. = Johnny@test.com

Records 3,001 to 6,000 need to have the same fields populated with different values:

'fromname' = Mick
'fromaddress' = Mick@test.com
'replyaddress'. = Mick@test.com

I need to split the list every 3,000 with other values.

Any help is appreciated. Thanks!

JT

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/08/2013 :  07:19:39  Show Profile  Reply with Quote
If you have a row id that can be used to identify the rows in the table, then you can do the following:
UPDATE tbl SET
	fromname = 
		CASE 
			WHEN rowId <= 3000 THEN 'Johnny'
			WHEN rowId <= 6000 THEN 'Mick'
			-- etc
		END
	-- etc
That can be made cleaner, for example, if you create a Reference table with a startid column, endId column and fromname, fromaddress, replyaddress columns. With such a table, you would do the following:
UPDATE t SET 
	fromname = r.fromname
	-- etc
FROM
	Tbl t
	INNER JOIN RefTable r ON 
		t.rowid BETWEEN r.startId AND r.endId
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/08/2013 :  07:44:55  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
Hi James,

Your query confuses me. I'm a complete novice.

Please could you write the entire query out for records 1 to 3000 and 3001 to 6000. Thanks (sorry if it's long winded)

JT
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/08/2013 :  08:09:03  Show Profile  Reply with Quote
Do you have Primary key column in this table?

UPDATE tbl SET
	fromname = 
		CASE 
			WHEN rowId BETWEEN 1 AND 3000 THEN 'Johnny'
			WHEN rowId  BETWEEN 3001 AND 6000 THEN 'Mick'
		END,
        fromaddress =
		CASE 
			WHEN rowId  BETWEEN 1 AND 3000 THEN 'Johnny@test.com'
			WHEN rowId  BETWEEN 3001 AND 6000 THEN 'Mick@test.com'
		END,
        replyaddress =
		CASE 
			WHEN rowId  BETWEEN 1 AND 3000 THEN 'Johnny@test.com'
			WHEN rowId  BETWEEN 3001 AND 6000 THEN 'Mick@test.com'
		END
FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY PrimaryKeyColName[blue]) rowid
          FROM [blue]TableName ) tbl


EDIT: I've changed condition in CASE statements... Try this once...

--
Chandu

Edited by - bandi on 03/12/2013 08:26:19
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/08/2013 :  09:28:08  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
Hi Chandu,

Your query disrupted my database :(

Fromname, fromaddress and reply address were all updated with Johnny.

How can I fix the query?

JT
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/08/2013 :  10:02:49  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
I've created this query to update fromname. I'd like to know how update d.fromaddress and d.replyaddress where row number is between 3000 and 6000

WITH fish AS
(

SELECT d.CONTACTID, d.LASTMAILED, b.EMAIL, b.SALUTATION, a.WEBADDRESS, a.WEBADDRESS2, a.WEBADDRESS3, a.WEBADDRESS4, d.ARIALUNSUBSCRIBE, d.LASTTITLE,
d.LASTMAILEDSEO, d.LASTCLICK1, d.LASTCLICK2, d.LASTCLICK3, d.LASTCLICK4, d.LASTOPEN, d.LASTHARDBOUNCE, d.LASTSOFTBOUNCE, b.DONOTSOLICIT, d.ABSPLIT, a.ACCOUNT,
d.BADEMAIL, b.FIRSTNAME, b.LASTNAME, d.DONE, d.LASTMAILED_CONTEXTUAL, d.LASTMAILED_RETARGETING, d.LASTMAILED_RON, d.LASTMAILED_CPC, d.LASTMAILED_SITESPECIFIC,
d.LASTMAILED_GUARDIAN, d.LASTMAILED_OBSERVER, d.LASTMAILED_SATIND, d.LASTMAILED_SUNIND, d.fromname, ROW_NUMBER() OVER (ORDER BY d.contactid) AS 'RowNumber'



FROM sysdba.ACCOUNT AS a LEFT OUTER JOIN
sysdba.CONTACT AS b ON a.ACCOUNTID = b.ACCOUNTID LEFT OUTER JOIN
sysdba.C_ACCOUNT1TO1 AS c ON c.ACCOUNTID = a.ACCOUNTID LEFT OUTER JOIN
sysdba.C_CONTACT1TO1 AS d ON d.CONTACTID = b.CONTACTID LEFT OUTER JOIN
sysdba.ADDRESS AS e ON e.ADDRESSID = a.ADDRESSID
WHERE (b.DONOTSOLICIT <> 't' )


update fish
set fromname = 'johnny t'

WHERE RowNumber BETWEEN 1 AND 3000;



JT

Edited by - Topaz on 03/08/2013 10:04:34
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 03/12/2013 :  07:00:57  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
The query below updates records 1 to 3000 with fromname and fromaddress = 'Johnny'.

How can I modify this query to also update records 3001 to 6000 with fromname and fromaddress = 'Mick'

Thanks for any help..



WITH emailrow AS

(

SELECT d.CONTACTID, d.LASTMAILED, b.EMAIL, b.SALUTATION, a.WEBADDRESS, a.WEBADDRESS2, a.WEBADDRESS3,
a.WEBADDRESS4, d.ARIALUNSUBSCRIBE, d.LASTTITLE, d.LASTMAILEDSEO, d.LASTCLICK1,
d.LASTCLICK2, d.LASTCLICK3, d.LASTCLICK4, d.LASTOPEN, d.LASTHARDBOUNCE, d.LASTSOFTBOUNCE,
b.DONOTSOLICIT, d.ABSPLIT, a.ACCOUNT, d.BADEMAIL, b.FIRSTNAME, b.LASTNAME, d.DONE,
d.FROMNAME, d.FROMADDRESS, d.REPLYADDRESS, d.TEMPLATESENDER, d.TEMPLATESENDER2, d.orgtype,
ROW_NUMBER() OVER (ORDER BY d.contactid) AS 'RowNumber'

FROM

sysdba.ACCOUNT AS a LEFT OUTER JOIN
sysdba.CONTACT AS b ON a.ACCOUNTID = b.ACCOUNTID LEFT OUTER JOIN
sysdba.C_ACCOUNT1TO1 AS c ON c.ACCOUNTID = a.ACCOUNTID LEFT OUTER JOIN
sysdba.C_CONTACT1TO1 AS d ON d.CONTACTID = b.CONTACTID LEFT OUTER JOIN
sysdba.ADDRESS AS e ON e.ADDRESSID = a.ADDRESSID

WHERE

(b.DONOTSOLICIT <> 't' OR b.DONOTSOLICIT IS NULL) AND (d.ARIALUNSUBSCRIBE = '0')
AND (d.DONE NOT LIKE 'T') AND (d.LASTSOFTBOUNCE LIKE '0') AND (d.LASTHARDBOUNCE LIKE '0')
AND (NOT (b.EMAIL LIKE '%aol.com%'))
AND (d.LASTMAILED <= DATEADD(DD, - 30, GETDATE()))
and (d.bademail like '0' or d.bademail is null)


update emailrow set

fromname = 'Johnny',
fromaddress = 'johnny@test.com'

WHERE RowNumber BETWEEN 1 AND 3000;

JT
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.08 seconds. Powered By: Snitz Forums 2000