updating 1000 rows...

Question

Post reply

updating 1000 rows...

Gideon Lamptey

Valued Member

Points: 51
More actions
October 17, 2005 at 5:34 am

#152586

hi all,
how do I update a particular column in one go? I need to update a particular col that has 1000 rows with different numbers ..pls help
example: update table categories
            set faxnumber= 1234567
            where cust_name =james
in this scenario I need to change the cust_name with different names (1000) but with same faxnumber

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Is there nothing else to identify these rows ?! A companyID perhaps ?! How many rows are there in all ?!

**ASCII stupid question, get a stupid ANSI !!!**

Papillon Hall of Fame Points: 3303 More actions · Answer 2

Hii!!!!

update table categories

set faxnumber= 1234567

where cust_name IN (Select cust_name From Table_Name/categories)

Table_Name is name of table where ur 1000 records are there or categories table itself I think..............

Regards,

Papillon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This will work... but if you ever have to do this more than once, I'd suggest writting it as an inner join.

Gideon Lamptey Valued Member Points: 51 More actions · Answer 4

thanks shashank...

This list was sent to me by the third party Application vendor.

there are 750 rows, but I am trying to Avoid typing that 750 names in the where clause.like this

where cust_name in ('james','john','charlie'.......) I thought there was a procedure which will pick up the names one by one.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

you don't have to type the individual names...

if you notice shashank's sql is..

update table categories
set  faxnumber= 1234567
where cust_name IN (SELECT CUST_NAME FROM TABLE_NAME/CATEGORIES)

**ASCII stupid question, get a stupid ANSI !!!**

Mackan SSC Rookie Points: 28 More actions · Answer 6

If you want to update ALL the rows in a table, you can just do an update without a where clause..

update categories set faxnumber = 1234567

Todd Painter-134230 SSC Veteran Points: 207 More actions · Answer 7

Are you saying that each person will get a new fax number? If that is the case, you would load a table (import_table) with two columns (name, fax) and then:

update <production_table>

set fax = b.fax

from production_table a, import_table b

where a.name = b.name

If the fax number will be the same for your whole list, a join may be faster than a substring as another poster stated above, but for 1000 rows this shouldn't be an issue.