Update 10000 fields - SQL Server Forums

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 10000 fields

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-15 : 06:36:14

I have a list of 10000 websites that need to be updated with certain data.

So my question is would i be able to get SQL to lookup a certain list of websites? And then me update the required fields?

Its prob an easy answer, thanks!

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 06:49:46

how is the data stored?

look at the UPDATE statement in Books Online. it should be simply a case of putting a where clause in your update.

if you post some info about your tables / data we can have a look and help you with the syntax

Em

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-15 : 07:06:50

we have all these fields:

UNIQUEID CREATETIME EDITTIME RECORDMANAGER Company Contact Address1 Address2 Address3 City County Postcode Country IDStatus Phone Fax MobilePhone Salutation LastReach LastAttempt LetterDate Title Mail17Date AltPhone FirstName Surname SoftBounce Mail17Title Department CREATEUSER RECORDOWNER WebSite ContactType Task ExpressEmail Mail16Title Mail16Date Artwork Mail18Date Mail18Title Mail19Date Mail19Title WebResearcher Mail1Date Mail1Title Mail2Date Mail2Title Mail3Date Mail3Title Mail4Date Mail4Title Mail5Date Mail5Title Mail6Date Mail6Title Mail7Date Mail7Title Mail8Date Mail8Title Mail9Date Mail9Title Mail10Date Mail10Title Mail11Date Mail11Title Mail12Date Mail12Title Mail13Date Mail13Title Mail14Date Mail14Title Blank Mail15Date Mail15Title Optin EmailAddress EDITUSER MANAGINGTEAM Sender1 Sender2 Sender3 Sender4 Sender5 Sender6 Sender7 Sender8 Sender9 Sender10 Sender11 Sender12 Sender13 Sender14 Sender15 Sender16 Sender17 Sender18 Sender19 HardBounce3 HardBounce4 HardBounce5 HardBounce6 HardBounce7 HardBounce8 HardBounce9 HardBounce10 HardBounce11 HardBounce12 HardBounce13 HardBounce14 HardBounce15 HardBounce16 HardBounce17 HardBounce18 HardBounce19 SoftBounce1 SoftBounce2 SoftBounce3 SoftBounce4 SoftBounce5 SoftBounce6 SoftBounce7 SoftBounce8 SoftBounce9 SoftBounce10 SoftBounce11 SoftBounce12 SoftBounce13 SoftBounce14 SoftBounce15 SoftBounce16 SoftBounce17 SoftBounce18 SoftBounce19 Mail1Click2 Mail1Click4 Mail2Click1 Mail2Click2 Mail1Click3 Mail2Click3 Mail2Click4 Mail3Click1 Mail3Click2 Mail3Click3 Mail3Click4 Mail4Click1 Mail4Click2 Mail4Click3 Mail4Click4 Mail5Click1 Mail5Click2 Mail5Click3 Mail5Click4 Mail6Click1 Mail6Click2 Mail6Click3 Mail6Click4 Mail7Click1 Mail7Click2 Mail7Click3 Mail7Click4 Mail8Click1 Mail8Click2 Mail8Click3 Mail8Click4 Mail9Click1 Mail9Click2 Mail9Click3 Mail9Click4 Mail10Click1 Mail10Click2 Mail10Click3 Mail10Click4 Mail11Click1 Mail11Click2 Mail11Click3 Mail11Click4 Mail12Click2 Mail12Click3 Mail12Click4 Mail13Click1 Mail13Click2 Mail13Click3 Mail13Click4 Mail14Click1 Mail14Click2 Mail14Click3 Mail14Click4 Mail15Click1 Mail15Click2 Mail15Click3 Mail15Click4 Mail16Click1 Mail16Click2 Mail16Click3 Mail16Click4 Mail17Click1 Mail17Click2 Mail17Click3 Mail17Click4 Mail18Click1 Mail18Click2 Mail18Click3 Mail18Click4 Mail19Click1 Mail19Click2 Mail19Click3 Mail19Click4 OpenEmail1 OpenEmail2 OpenEmail3 OpenEmail4 OpenEmail5 OpenEmail6 OpenEmail7 OpenEmail8 OpenEmail9 OpenEmail10 OpenEmail11 OpenEmail12 OpenEmail13 OpenEmail14 OpenEmail15 OpenEmail16 OpenEmail17 OpenEmail18 OpenEmail19 Mail12Click1 test sergiotest HardBounce2 mail1click1 HardBounce1 Done Unsubscribe EmailSubject1 EmailSubject2 EmailSubject3 EmailSubject4 EmailSubject5 EmailSubject6 EmailSubject7 EmailSubject8 EmailSubject9 EmailSubject10 EmailSubject11 EmailSubject12 EmailSubject13 EmailSubject14 EmailSubject15 EmailSubject16 EmailSubject17 EmailSubject18 EmailSubject19 Template1 Template2 Template3 Template4 Template5 Template6 Template7 Template8 Template9 Template10 Template11 Template12 Template13 Template14 Template15 Template16 Template17 Template18 Template19

And basically what i want to lookup a list of 10000 websites and update the mail3date field. HTH?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 07:13:16

which 10000? what identifies them? update mail3date with what? a constant? something different for each record?

basic update syntax...

update urTable
set mail3date = {some date presumably??}
where AColumn = {some criteria}

did you look in Books Online?

Em

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-15 : 07:35:38

I can never understand BOL as when i search for something it brings up unrealted examples (prob just me not using it right)

Ok, going on statement

update wce_contact
set mail3date = {14/11/2007}
where website = {www.anywebsite.co.uk, www.anywebsite2.co.uk, www.anywebsite3.co.uk}

is that how it should look?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 07:38:31

you don't need the {}, i just put them in to show you where stuff goes

should be more like...

update wce_contact
set mail3date = '20071114'
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 07:41:54

of course with 10000 to update it's gonna be a long haul that way. is there anything else that identifies which one's to update?
something common about their name?
do you have them in a seperate list / table that you could join to?

something like...


update wce_contact
set mail3date = '20071114'
from wce_contact t1
  join XTable t2 on t1.website = t2.webstite

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-15 : 07:48:17

if only there was an 'identifier'... this is why im resorting to doing it this way.

If i wanted to include LastReach in there would it look like this

update wce_contact
set mail3date = '20071114'
AND SET LastReach = '20071114'
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 07:48:34

oh, and a tip with BOL. if you want help with say 'UPDATE'... type UPDATE into your query window highlight it or position the cursor in it, then hit F1

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 07:51:17

no, it would be...

update wce_contact
set mail3date = '20071114' , LastReach = '20071114'
where website in (....long long list

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources