Clever Geek Handbook

SQL query to update table columns where input parameters are not null?

I have a table that has the following columns.

Ticket_id (Primary key, Int)
Attachment1 (varchar)
Attachment2 (varchar)
Attachment3 (varchar)
Attachment4 (varchar)
Attachment5 (varchar)

I am writing a stored proc to update this table. there are 6 inputs for the above columns.

@Attached_File1 VARCHAR(MAX),
@Attached_File2 VARCHAR(MAX),
@Attached_File3 VARCHAR(MAX),
@Attached_File4 VARCHAR(MAX),
@Attached_File5 VARCHAR(MAX),
@Ticket_ID BIGINT

I want to write a sql query that will update the table with the values ​​given in the input parameters. BUT I shouldn't be overriding nullable attachment columns. I mean, I only need to use parameters that contain data.

For example, if the ha table contains the string [10, "aaa", "bbb", null, null, null] and the input parameters are (10, null, null, "ccc", "ddd", null), then after the update the line becomes [10, "aaa", "bbb", "ccc", "ddd", null]

How do I check for null / empty strings and generate an update request for that?

+3


source to share


4 answers


You can use ISNULL

UPDATE table_name SET
  Attachment1 = ISNULL(Attachment1, @Attached_File1),
  Attachment2 = ISNULL(Attachment2, @Attached_File2),
  Attachment3 = ISNULL(Attachment3, @Attached_File3),
  Attachment4 = ISNULL(Attachment4, @Attached_File4),
  Attachment5 = ISNULL(Attachment5, @Attached_File5)
WHERE Ticket_id = @Ticket_ID


This solution does not overwrite the existing value with the new one. If you want to do this, you have to switch the values ​​around:

ISNULL(@Attached_File1, Attachment1)
+5


source


You can try this:



update MY_TABLE set Attached_File1 = ISNULL(@parameter1, Attached_File1);
+2


source


You can simply:

SET 
   AttachmentX = ISNULL(AttachmentX, @Attached_FileX)

or if the parameters can be empty strings

   AttachmentX = ISNULL(AttachmentX, NULLIF(@Attached_FileX, ''))
+1


source


In your expression, UPDATE

you can use an operator COALESCE

, for example:

SET Attachment1 = COALESCE(@Attached_File1, Attachment1)

If @Attached_File1

- NULL

, then the current value Attachment1

will actually remain unchanged.

+1


source






More articles:


All Articles