3 Replies - 2081 Views - Last Post: 21 July 2013 - 05:29 AM

#1 college_trained   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 49
  • Joined: 10-January 10

T sql update some but not all columns in a table

Posted 20 July 2013 - 04:12 PM

Hey all,

I have a simple question today. This is not something I've come across before as I have always updated all columns in a table. I have a table with about 20 columns but at this point in time, I only need to update about 10 of them.

My question is, can I write a stored procedure that only updates certain columns and not others or do I need to have all my columns in an update statement? I have tried creating a stored procedure with only the needed columns, and when I run it, it says it completed successfully but the data never updates.

Any help would be greatly appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: T sql update some but not all columns in a table

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,342
  • Joined: 12-December 12

Re: T sql update some but not all columns in a table

Posted 20 July 2013 - 04:21 PM

You'll need to provide more information to discover why your sp didn't work, including the procedure itself. If there is no error then it is most likely that the WHERE criteria doesn't correctly identify records to update.

And, yes, it is very common to only update certain fields. Of course, the other fields will need to allow NULL or have a DEFAULT value, and these would be omitted from the UPDATE statement.

This post has been edited by andrewsw: 21 July 2013 - 05:30 AM

Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: T sql update some but not all columns in a table

Posted 21 July 2013 - 04:31 AM

Updating just one or two fields is very common. e.g.
update Person
  set Phone='212-555-1234'
  where PersonId=42




View Postandrewsw, on 20 July 2013 - 07:21 PM, said:

Of course, the other fields will need to allow NULL or have a DEFAULT value, and these would be omitted from the UPDATE statement.


This doesn't make much sense in the context of an UPDATE statement. No other fields need to allow anything. Only those in the SET will be affected by the call.
Was This Post Helpful? 1
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,342
  • Joined: 12-December 12

Re: T sql update some but not all columns in a table

Posted 21 July 2013 - 05:29 AM

Doh! Of course baavgai, thank you. I was thinking of INSERT.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1