how can I ignore this and update only the data that is not empty?

Question

Hey
I have a request:
UPDATE category
SET name = (?) , title = (?), image = (?)
WHERE id = (?)

Instead of (?) I substitute values, but sometimes they can be empty and the request is not accepted, how can I ignore this and update only the data that is not empty?

Rajanikant Hawaldar · Answer

use COALESCE() or validate values as suggested by other experts

Sachin Singh · Answer

If you are facing this situation then you are doing something wrong. While updating the record we never send the null values to procedures. The general step for update involves Retrieving all the records based on identifier and then update. public void update(Employee emp) { var ObjToUpdate= Connection.QueryAsaync (" select * from table where id=@id, new {@id=emp.Id}).SingleOrDefault(); "); ObjToUpdate.Name=emp.Name??ObjToUpdate.Name; ObjToUpdate.Title=emp.Title??ObjToUpdate.Title; Connection.Execute( Update table set Name=@Name, Title=@Title where id=@id, new {@Name=ObjToUpdate.Name,Title=ObjToUpdate.Title,@id=emp.Id}); } I have used dapper , but the approach should be same.

Rijwan Ansari · Answer

Hi, If there are no values then update the SQL Scripts like as shown below: // All values example UPDATE category SET name = "Value" , title = "value1" , image = "Value2" WHERE id = 12; // no title, image values example UPDATE category SET name = "Value" WHERE id = 12; // no name value example UPDATE category SET title = "value1" , image = "Value2" WHERE id = 12; Likewise, you can modify the SQL Script based on values.

how can I ignore this and update only the data that is not empty?

Answers (3)