仅当字段包含null或0时才更新MYSQL字段

Question

I want to insert data into a table, then if the table already has that same input value of rName then it is to update this row (as rName is a unique index) which it all does well.

But on the UPDATE I would like it to only UPDATE the fields IF the field doesn’t already have something in it (NULL).

INSERT INTO $table  
(`rDate`, `rName`, `rFormat`, `rText`) VALUES 
(CURRENT_TIMESTAMP, 'name3', 'formate22') ON DUPLICATE KEY UPDATE 
 rFormat=VALUES(rFormat), 
 IF('rFiles'=NULL,rFiles=VALUES(rFiles), rFiles=VALUES(rSizeMB)), 
 rText=VALUES(rText)

Also is this the best to do what I’m doing? It seems a bit repudiative, as I have about twice the amount of fields then shown here, I just cut it down for the question.

Answer 1

Try this:

...
ON DUPLICATE KEY UPDATE
rFormat=VALUES(rFormat),
rFiles=IF(ISNULL(rFiles), VALUES(rFiles), VALUES(rSizeMB)),
rText=VALUES(rText);

I haven't used more advanced MySQL stuff (like IF) in a bit, so I'm not positive about this, but it should be about right. The key part is setting rFiles to the value returned by the IF.