issue with adding another column in a table

  • Hi,

    i have a table named as Book_master_tbl having only 10 records and as per design I need to add another column to this table called reserve with type int but when I take it design mode and added the column so no issue raised but when i try to select ist 100 rows so it displays all the records with showing Null in reserve column which seems ok but when I type the above select query so that column is red underline saying ' Invalid column name reserve' but in the output of the select query it is shown with Null values in it.

    Now from the front end when I try to update its value so it doesn't, the update query is as follows. where is the issue ??

    UPDATE book_master_tbl SET current_stock=current_stock - 1, reserve=reserve + 1 WHERE book_id=1;
  • The red underline from Intellisense is likely just from its cache not being refreshed; it often won't recognize newly created objects. If, as you suggest, the query completes without error, then that is not a problem.

    If all rows have NULL for reserve, then NULL+1 will return NULL, so the value will not change from that UPDATE.

    What exactly are you trying to accomplish with your UPDATE (sample data with expected results would be helpful).

    Cheers!

     

  • Most likely refreshing intellisense will clear the "error". It's more a warning, not an error, though; like Google Chrome telling you that "colour" is wrong because it defaults to American even when the OS tells it the user is English. 😉

    Most likely the column has been created in a separate session, and since intellisense initially cached the object names in the instance. You can refresh intellisense with Ctrl+Alt+R, and the line should go away. The only time that wouldn't be the case is in your are creating the column in the set of batches you have open, and it's being created in deferred statement, such as EXEC sys.sp_executesql N'ALTER TABLE dbo.MyTable ADD MyColumn int;';, as intellisense won't read inside the deferred statement, and thus state the column "doesn't exist".

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • The UPDATE "doesn't work" because since reserve is null, it will stay null, even though you think you "added 1 to it".  NULL + 1 = NULL.

    If you prefer, you can set the column to zero so that you can add to it:

    UPDATE dbo.Book_master_tbl

    SET reserve = 0

    WHERE reserve IS NULL

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Yes Jacob,

    what i am trying to do is that I m working on a library management automation system. Here a member can reserve a book for 24 hrs only when these 24 hrs elapsed so the reservation is expired. with reservation what i do it to decrement current book stock by 1 and after reservation expires, it is incremented by 1. Now i want to add another column in book_master_tbl (where all book details exist like name, genre, author, etc) named as 'Reserve' and it will be an Int type and it will tell me which book is how many times reserved so far. so if book_id has been reserved 50 times by members and whether the book was issued or reservation expired so 50 will be telling me that how many time the book was reserved

    so upon book reservation, i want to increment the ' Reserve' column by 1 all the time  . so a simple update statement at the time of reservation in which the same book is decremented by 1 from current stock (which is already working) and Reserve column incremented by 1 for that particular Book_id.

    how to initialize the Reserve column by zero or any other idea

    i am new to SQL server

     

    Regards,

  • Ah, if you just want to initialize the new column to zero, then Scott's post has you covered.

    Cheers!

  • Yeagh Thank You Scott. i did it as u suggested and it worked .Thank You

  • But that was not the end of the story. even I did it now but the issue was that I have already records in my table and I want this new column to be not Null i.e it must have some value in it but due to already present data it was not allowing me to make it not Null so i use Scotts advise and once i put all zero in it then i change constraint from all Null to Not Null and it worked and Null+1=Null was also information for me.

    Sorry Jacob  if I bothered You

     

    Cheers!

     

  • No worries! I'm glad you got it sorted out.

     

    Cheers!

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

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