Post reply

updating 1000 rows...

  • 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

     

               

  • October 17, 2005 at 6:30 am

    #597569

    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 !!!**

  • October 17, 2005 at 6:36 am

    #597570

    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

  • October 17, 2005 at 6:40 am

    #597571

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

  • October 17, 2005 at 8:07 am

    #597625

    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.

  • October 17, 2005 at 8:21 am

    #597631

    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 !!!**

  • October 18, 2005 at 8:25 am

    #597940

    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

     

  • October 18, 2005 at 4:12 pm

    #598080

    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.

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

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