locked
I need to update the table where I may or may not update all columns, so how can I do that RRS feed

  • Question

  • Hi, I have a table which has some columns, in this table i am trying to update the table which is frequent process. So, here i may update some columns or all depending on my requirement for that particular time. So, now how can i write my update query here which involves all the columns, but if i do not need for particular column to update , then it should leave the value of the column as it is. i am little confused now how to do it, can I use stored procedure or with in the query? Can any one please let me know on this. i am giving the columns name, 

    SubSystemName PROV_TAX_ID Updated Bucket Prov_Type Act_SQS_Amt

    Deal_Name.

    So, these are the columns, how can I write the update query for it

    Tuesday, April 8, 2014 8:33 PM

Answers

  • instead of taking overhead of generating the dynamic Update SQL statement I would prefer to go for the full Update statement where I am updating ALL Columns of the table. for certain case where I do not want to update few columns on table I can cover those scenario in my update query. 

    I agree with other users up to certain extent that why we should update all column when even not needed. but I also consider that communication between Application and Data access layer should be more cleaner and easy to debug.

    in the below approach I am creating a Upsert API, for created sample table. notice that I have used all columns of table a s parameter.

    Except the RollNUmber All other Columns: "Name","City", "Country" are NULL able. that means , user can actually update a column value from xyz to NULL. and in such cases it is difficult to find out weather user do not wants to update this column or s/he wants to update the column value to NULL.

    and hence we need another indicative value to deiced if user want to skip update for specific column or not.

    I have taken value -1 as a indicator that user do not wants to update specific column.

    If RollNumber itself is NULL that means it is new record to be inserted.

    but if the Rollnumber is Given then user wants to update data for that RollNumber.

    you can check the demo here on SQL Fiddle i have used SQL 2008 environment for this

    Demo of Partial and Complete Update - SQL FIDDLE

    you can also use below script to replicate in your local env.

    Creating the Table and Procedure and Inserting some sample data.

    	IF object_id('TestUpdate','u') IS NOT NULL
    		DROP TABLE TestUpdate
    	GO
    	CREATE TABLE TestUpdate
    	(
    		rowid		INT	NOT NULL IDENTITY
    		,rollnum	INT	NOT NULL
    		,NAME		sysname	NULL
    		,city		sysname	NULL
    		,country	sysname	NULL
    	)
    	GO
    	INSERT INTO dbo.TestUpdate( rollnum, NAME, city, country )
    				SELECT 1,'a','city1','country1'
    	UNION all	SELECT 2,'b',NULL,NULL
    	UNION all	SELECT 3,'c','city1',NULL
    
    
    	IF object_id('Pr_TestUpdate_Upsert','p') IS NOT NULL
    		DROP PROCEDURE Pr_TestUpdate_Upsert
    	GO
    	CREATE PROCEDURE Pr_TestUpdate_Upsert
    	(
    		@rollnum	INT=NULL
    		,@NAME		sysname='-1'
    		,@city		sysname='-1'
    		,@country	sysname='-1'
    	)
    	AS
    	BEGIN
    		--IF @rollnum  IS NULL that eans it is a new record Insert
    		IF @rollnum IS NULL
    		BEGIN		
    			SELECT @rollnum = MAX(rollnum)+1 FROM TestUpdate		
    			INSERT INTO dbo.TestUpdate( rollnum, NAME, city, country )
    			SELECT @rollnum, @NAME, @city, @country
    		END
    		ELSE
    		BEGIN
    			UPDATE TestUpdate SET 
    				NAME = CASE WHEN @name='-1' THEN NAME ELSE @NAME END
    				,city = CASE WHEN @city='-1' THEN city ELSE @city END
    				,country = CASE WHEN @country='-1' THEN country ELSE @country END
    			WHERE rollnum = @rollnum			
    		END
    	END
    	GO
    

    Inserting new Data and Updating existing data.

    select * from TestUpdate
    GO
    
    --insert a new RollNumber with AllOther  as null values
    EXECUTE Pr_TestUpdate_Upsert
    	@rollnum = NULL, -- int
        @NAME = NULL, -- sysname
        @city = NULL, -- sysname
        @country = NULL -- sysname
    GO
    
    select * from TestUpdate
    GO
    
    
    --Update a city from NULL to 'ABC' where RollNumber=2 
    EXECUTE Pr_TestUpdate_Upsert
    	@rollnum = 2, -- int
        @NAME = '-1', -- sysname
        @city = 'ABC', -- sysname
        @country = '-1' -- sysname
    GO
    
    select * from TestUpdate
    GO
    
    --Update a Country from NULL to 'India' and City from 'city1' to NULL where RollNumber=3
    EXECUTE Pr_TestUpdate_Upsert
    	@rollnum = 3, -- int
        @NAME = '-1', -- sysname
        @city = NULL, -- sysname
        @country = 'India' -- sysname
    GO
    
    select * from TestUpdate
    GO
    


    Anup Shah

    • Proposed as answer by Elvis Long Tuesday, April 22, 2014 2:35 AM
    • Marked as answer by Kalman Toth Tuesday, April 29, 2014 8:05 PM
    Thursday, April 17, 2014 9:26 PM

All replies

  • You can generate your update statement dynamically, e.g.

    create procedure UpdateMyTable

    (@Col1 int = NULL,

      @col2 varchar(12) = null,

      etc.)

    AS

    set nocount on

    declare @SQL nvarchar(max)

    set @sql =  case when @Col1 IS NOT NULL then ', Col1 = @Col1' ELSE '' END + 

    case when @Col2 IS NOT NULL then ', Col2 = @Col2' else '' END 

    IF LEN(@SQL) > 0

       begin

           set @SQL = 'UPDATE MYTable SET ' + stuff(@SQL, 1,0, '') 

           print @SQL -- verify

           execute sp_ExecuteSQL @SQL, N'@col1 int, @col2 varchar(12)', @Col1, @Col2, ...

    end

    ----------------

    The above assumes that if you don't pass parameter to the SP (and it will be default NULL value), then you don't want to update that column.

    It would not work if your columns are nullable and NULL is a possible choice for a new value.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 8, 2014 8:50 PM
  • Yes my columns are nullable, thats the problem. How can i overcome that?
    Tuesday, April 8, 2014 8:55 PM
  • If there are some default values you can use and you know that you will never use these values for the column values, then you can use the same idea as I specified above.

    If not, then I do not really see a big problem of updating all columns even if the value in the column didn't change. I don't think it really matters for SQL Server and although I don't know SQL Server internals, I think as long as you don't use varchar(max) / varbinary(max) or any other exotic column types, listing all columns in update or only few columns in update will make no difference.

    Someone from MS team correct me, if I am wrong.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 8, 2014 9:02 PM
  • >I do not really see a big problem of updating all columns even if the value in the column didn't change

    That is not good practice.

    Just update the column to be changed.

    UPDATE blog:

    http://www.sqlusa.com/articles2005/sqlupdate/


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Thursday, April 17, 2014 7:29 PM
  • How would you generate the UPDATE command for only changed values?

    BTW, if you're using .NET, you can use DataAdapter and SqlCommandBuilder and the generated command will only include columns that were updated. See my last reply in this thread

    What do I need to make this class to be able to Send Updates back to SQL Server?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 17, 2014 7:38 PM
  • >How would you generate the UPDATE command for only changed values?

    Use metadata from INFORMATION_SCHEMA views.

    INFORMATION_SCHEMA views usage examples: http://www.sqlusa.com/bestpractices2005/informationschema/


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Edited by Kalman Toth Friday, April 18, 2014 12:45 AM
    Thursday, April 17, 2014 7:40 PM
  • Kalman,

    Your answer doesn't explain anything. Although we don't really know the OP's problem, I guess.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 17, 2014 7:52 PM
  • instead of taking overhead of generating the dynamic Update SQL statement I would prefer to go for the full Update statement where I am updating ALL Columns of the table. for certain case where I do not want to update few columns on table I can cover those scenario in my update query. 

    I agree with other users up to certain extent that why we should update all column when even not needed. but I also consider that communication between Application and Data access layer should be more cleaner and easy to debug.

    in the below approach I am creating a Upsert API, for created sample table. notice that I have used all columns of table a s parameter.

    Except the RollNUmber All other Columns: "Name","City", "Country" are NULL able. that means , user can actually update a column value from xyz to NULL. and in such cases it is difficult to find out weather user do not wants to update this column or s/he wants to update the column value to NULL.

    and hence we need another indicative value to deiced if user want to skip update for specific column or not.

    I have taken value -1 as a indicator that user do not wants to update specific column.

    If RollNumber itself is NULL that means it is new record to be inserted.

    but if the Rollnumber is Given then user wants to update data for that RollNumber.

    you can check the demo here on SQL Fiddle i have used SQL 2008 environment for this

    Demo of Partial and Complete Update - SQL FIDDLE

    you can also use below script to replicate in your local env.

    Creating the Table and Procedure and Inserting some sample data.

    	IF object_id('TestUpdate','u') IS NOT NULL
    		DROP TABLE TestUpdate
    	GO
    	CREATE TABLE TestUpdate
    	(
    		rowid		INT	NOT NULL IDENTITY
    		,rollnum	INT	NOT NULL
    		,NAME		sysname	NULL
    		,city		sysname	NULL
    		,country	sysname	NULL
    	)
    	GO
    	INSERT INTO dbo.TestUpdate( rollnum, NAME, city, country )
    				SELECT 1,'a','city1','country1'
    	UNION all	SELECT 2,'b',NULL,NULL
    	UNION all	SELECT 3,'c','city1',NULL
    
    
    	IF object_id('Pr_TestUpdate_Upsert','p') IS NOT NULL
    		DROP PROCEDURE Pr_TestUpdate_Upsert
    	GO
    	CREATE PROCEDURE Pr_TestUpdate_Upsert
    	(
    		@rollnum	INT=NULL
    		,@NAME		sysname='-1'
    		,@city		sysname='-1'
    		,@country	sysname='-1'
    	)
    	AS
    	BEGIN
    		--IF @rollnum  IS NULL that eans it is a new record Insert
    		IF @rollnum IS NULL
    		BEGIN		
    			SELECT @rollnum = MAX(rollnum)+1 FROM TestUpdate		
    			INSERT INTO dbo.TestUpdate( rollnum, NAME, city, country )
    			SELECT @rollnum, @NAME, @city, @country
    		END
    		ELSE
    		BEGIN
    			UPDATE TestUpdate SET 
    				NAME = CASE WHEN @name='-1' THEN NAME ELSE @NAME END
    				,city = CASE WHEN @city='-1' THEN city ELSE @city END
    				,country = CASE WHEN @country='-1' THEN country ELSE @country END
    			WHERE rollnum = @rollnum			
    		END
    	END
    	GO
    

    Inserting new Data and Updating existing data.

    select * from TestUpdate
    GO
    
    --insert a new RollNumber with AllOther  as null values
    EXECUTE Pr_TestUpdate_Upsert
    	@rollnum = NULL, -- int
        @NAME = NULL, -- sysname
        @city = NULL, -- sysname
        @country = NULL -- sysname
    GO
    
    select * from TestUpdate
    GO
    
    
    --Update a city from NULL to 'ABC' where RollNumber=2 
    EXECUTE Pr_TestUpdate_Upsert
    	@rollnum = 2, -- int
        @NAME = '-1', -- sysname
        @city = 'ABC', -- sysname
        @country = '-1' -- sysname
    GO
    
    select * from TestUpdate
    GO
    
    --Update a Country from NULL to 'India' and City from 'city1' to NULL where RollNumber=3
    EXECUTE Pr_TestUpdate_Upsert
    	@rollnum = 3, -- int
        @NAME = '-1', -- sysname
        @city = NULL, -- sysname
        @country = 'India' -- sysname
    GO
    
    select * from TestUpdate
    GO
    


    Anup Shah

    • Proposed as answer by Elvis Long Tuesday, April 22, 2014 2:35 AM
    • Marked as answer by Kalman Toth Tuesday, April 29, 2014 8:05 PM
    Thursday, April 17, 2014 9:26 PM