Update first 3000 rows, then next 3000 and so on

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

Update first 3000 rows, then next 3000 and so on

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-08 : 07:12:39

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-08 : 07:19:39

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

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-08 : 07:44:55

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-08 : 08:09:03

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

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-08 : 09:28:08

Hi Chandu,

Your query disrupted my database :(

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

How can I fix the query?

JT

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-08 : 10:02:49

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

Topaz
Posting Yak Master

199 Posts

Posted - 2013-03-12 : 07:00:57

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources