| Author |
 Topic  |
|
|
Topaz
Posting Yak Master
United Kingdom
199 Posts |
 Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 03/08/2013 : 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
United Kingdom
199 Posts |
Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/08/2013 : 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 |
Edited by - bandi on 03/12/2013 08:26:19 |
|
|
|
Topaz
Posting Yak Master
United Kingdom
199 Posts |
Posted - 03/08/2013 : 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
United Kingdom
199 Posts |
Posted - 03/08/2013 : 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 |
Edited by - Topaz on 03/08/2013 10:04:34 |
|
|
|
Topaz
Posting Yak Master
United Kingdom
199 Posts |
Posted - 03/12/2013 : 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 |
|
|
| |
Topic |
|
|
|
Update first 3000 rows, then next 3000 and so on
