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

Daanish G

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?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar4y ago
Accepted answer
use COALESCE() or validate values as suggested by other experts
+4
Sachin Singh4y ago
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.
+3
Rijwan Ansari4y ago
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.
+3