none
is possible that an Tsql update does not update all fields in the query's update? RRS feed

  • Question

  • Hi,

    I have an update query like this:

    update MyTable set Field_1 = Field_1 - @DecimalVar1, Field_2 = Field_2 - @DecimalVar2, Field_3_datetime = getadate() where .....

    Filds_1,2 are decimal(18,4) and @DecimalVar1,2 too.

    some times happens that all the fields are updated except "Field_2" (always this).

    i have a trigger in the table but it isn't involved in that update...

    also check the update with @@Rowcount and it's ok.

    is possible that the update query doesn't update all filds??? 

    Wednesday, September 11, 2019 7:55 AM

All replies

  • >>s possible that the update query doesn't update all filds??? 

    No, it should update.....Is that possible you post here sample data ti reproduce the issue?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 11, 2019 7:59 AM
    Answerer
  • Hi Alby_net

     

     The update query can update all field if you would like to do that. Here is an example .

     

    I'm not sure why you didn't succeed. Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test. Also , you mentioned trigger. Can you give me some information about it.

     
    IF OBJECT_ID('MyTable') IS NOT NULL drop table  MyTable  
    go 
    
    create table MyTable  
    (Field_1 decimal(18,4),
    Field_2 decimal(18,4),
    Field_3_datetime datetime)
    insert into MyTable values
    (1.2565,2012.21,'2019-5-23 2:20:30.000'),
    (1.2565,254.21,'2019-7-23 11:25:30.000'),
    (25.2654,2.21,'2019-5-25 2:20:56.000')
    go
    declare @DecimalVar1 decimal(18,4)=1.2
    declare @DecimalVar2 decimal(18,4)=1.1
    update MyTable 
    set Field_1 = Field_1 - @DecimalVar1, 
    Field_2 = Field_2 - @DecimalVar2, 
    Field_3_datetime = getdate() 
    where Field_1=1.2565
    select @@ROWCOUNT
    /*
    -----------
    2
    */
    
    select * from MyTable
    /*
    Field_1                                 Field_2                                 Field_3_datetime
    --------------------------------------- --------------------------------------- -----------------------
    0.0565                                  2011.1100                               2019-09-11 16:08:51.043
    0.0565                                  253.1100                                2019-09-11 16:08:51.043
    25.2654                                 2.2100                                  2019-05-25 02:20:56.000
    */

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 11, 2019 8:14 AM
  • thank you for answer.

    the update i referred to, is a part of stored procedure. is complex to me hexhibit all the code and explane that.

    what i wanted to know if is possible that an update fails for only one fild....

    Wednesday, September 11, 2019 8:22 AM
  • No and No...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 11, 2019 8:24 AM
    Answerer
  • Hi Alby_net

     

    Thank you for your reply.

     

    I think it is not possible.

     

    Please check your stored procedure. Is there any other condition for  the field in your script ? Or it has been updated but its value hasn't changed because of some condition.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 11, 2019 8:37 AM
  • Maybe @DecimalVar2 sometimes is zero?

    Wednesday, September 11, 2019 8:44 AM
  • Maybe @DecimalVar2 sometimes is zero?

    i thought but doesn't possible... i'll introduce a control of that variable before update.

    thanks for help me


    Wednesday, September 11, 2019 9:17 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 12, 2019 8:20 AM
  • Good day Alby,

    As other said the update cannot fail for one column and succeed for another, but what can be is that the logic you use for the update sets an unexpected value to the column  (which include set the value to the existing value which seems like there was not update). In order to solve your issue we will need to see the exact query you use and reproduce the issue. This mean that you need to provide DDL+DML and the exact full query

    Without this we can answer in short that the answer is NO


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, September 14, 2019 12:50 AM
    Moderator
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    4 hours 55 minutes ago