CODEWITHSUNDEEP
sql-serversql-update
Pawan Agrawal
Pawan Agrawal

Reputation: 283

SQL Query for update a record with null values

I want sql query for update a record. For example there are 3 columns in my table -

Name , Address, Email

and when I update any one column other columns value should be null without set null to them. Like 

Update tbl_Student set Name = 'XYZ' where id = 1

In above case only Name column should update and other column should update by null.

FYI,

As there are two many columns that I need to set null that's why I wanted some kind of query to do that. Like -

ID    ADDRESS      NAME      EMAIL     
1     PARK ROAD    JOHN      [email protected]

And I want to update only name and want rest of the fields should be null.Like

Update Table SET NAME = 'NICK' WHERE ID = 1

Then updated output should be -

ID    ADDRESS      NAME      EMAIL     
1     NULL         NICK      NULL

Upvotes: 3

Views: 12284

Answers (5)

sapi
sapi

Reputation: 244

DECLARE @NVC_Query nvarchar(max) = ''

SET @NVC_Query = 'UPDATE  Tour_Table
SET'

 select @NVC_Query += '
    ' + columns.name + ' = NULL ,'
from sys.tables INNER JOIN
     sys.columns
ON tables.object_id = columns.object_id
WHERE tables.name = 'Your table'
  AND columns.name <> 'The only col you want to update'

SET  @NVC_Query += '
     your colum = your_val
WHERE Yourcondition'

--SELECT @NVC_Query
EXEC SP_EXECUTESQL @NVC_Query

Use dynamic SQL for what you are trying to accomplish. It will be much easier than all of the previous answers

Upvotes: 0

Sandeep Kumar
Sandeep Kumar

Reputation: 1202

You can create a trigger for this.

CREATE TRIGGER [dbo].[TRG_UPD_STUDENT] ON [dbo].[tbl_Student]
FOR UPDATE
AS

IF UPDATE(Name) and  (inserted.name <> NULL)
 BEGIN
  Update tbl_Student set Address = NULL, Email = NULL where id = inserted.id
 END

IF UPDATE(Address) and (inserted.Address <> NULL)
 BEGIN
  Update tbl_Student set name = NULL, Email = NULL where id = inserted.id
 END

IF UPDATE(Email) and (inserted.Email <> NULL)
 BEGIN
 Update tbl_Student set name = NULL, Address = NULL where id = inserted.id
 END

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

My suggestion is, delete that record and insert a new one with only the column have value within a transaction. Because, i think you don't want to set NULL to the column id (may be some other column(s) also).

Sample:

DELETE FROM tbl_Student WHERE id = 1
INSERT INTO tbl_Student(id,Name) VALUES(1,'XYZ') --All other column will be nullable

Note:- If the id column is a IDENTITY, then you have to manage it with SET IDENTITY_INSERT

Upvotes: 0

besciualex
besciualex

Reputation: 1892

One quick ideea that would acomplish what you want is to have two statements:

  1. one that makes null everything ( except the id ) => Put a trigger on this one which executes before the second Update statement.

  2. and another one that updates the column you need with actual value.

You may reconsider the table structure to a simpler one:

ID | Column_Name | Column_Value

instead of having an enormous table with NULL columns like:

ID | COLUMN_1 | COLUMN_2 | ..... | COLUMN N
--------------------------------------------
2  |   NULL   |   NULL   |       | "string"

Upvotes: 0

StackUser
StackUser

Reputation: 5398

Try like this,

    --Case-1
If @NAME is not null
UPDATE tbl_Student
SET NAME = @NAME
    ,Address = null
    ,Email = null
WHERE id = 1
--Case-2
If @Address is not null
UPDATE tbl_Student
SET NAME = null
    ,Address = @Address
    ,Email = null
WHERE id = 1
--Case-3
If @Email is not null
UPDATE tbl_Student
SET NAME =null
    ,Address = null
    ,Email = @Email
WHERE id = 1

Upvotes: 0

Related Questions