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 |
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.comRecords 3,001 to 6,000 need to have the same fields populated with different values:'fromname' = Mick'fromaddress' = Mick@test.com'replyaddress'. = Mick@test.comI 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 -- etcFROM 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' ENDFROM ( 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 6000WITH 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.ADDRESSIDWHERE (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 JOINsysdba.CONTACT AS b ON a.ACCOUNTID = b.ACCOUNTID LEFT OUTER JOINsysdba.C_ACCOUNT1TO1 AS c ON c.ACCOUNTID = a.ACCOUNTID LEFT OUTER JOINsysdba.C_CONTACT1TO1 AS d ON d.CONTACTID = b.CONTACTID LEFT OUTER JOINsysdba.ADDRESS AS e ON e.ADDRESSID = a.ADDRESSIDWHERE (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 |
|
|
|
|
|
|
|