SQLite: INSERT or REPLACE certain columns, don't overwrite others with NULL?

Question

Imagine I have a table (refrigerator) with columns: (date(pk), fruits, veggies, starches, sweets). Here's an example row:

date     | fruits | veggies | starches | sweets 
20180220 | melon  | potato  |  pasta   | fudge

I want to insert or replace only certain columns in refrigerator with the following:

> INSERT or REPLACE into refrigerator (date, fruits, veggies, starches) VALUES ("20180220", "apple", "carrot", "bread")

When I do this, if I have not specified a value for (sweets), it is overwritten with NULL.

date     | fruits | veggies | starches | sweets 
20180220 | apple  | carrot  |  bread   | NULL

What would be the proper way to do this that doesn't replace values that I've specified?

I'm sure this is something blatantly obvious. Feel free to laugh at me.

Answer 1

Now it works as intended, and will also INSERT the row if it does not exist. It won't function properly without the UNION ALL. The first SELECT uses COALESCE() to preserve existing column values in the case where a row does exist. The second SELECT with NOT EXISTS covers the case where the row does not exist, so it constructs one. Therefore, the UNION ALL only ever returns 1 row: the "merged" row from an existing row, or a brand new one. Then, REPLACE INTO replaces this merged/created row back into the table by primary key.

CREATE TABLE refrigerator
  (
  date TEXT(8) NOT NULL PRIMARY KEY
  , fruits TEXT(50) NULL
  , veggies TEXT(50) NULL
  , starches TEXT(50) NULL
  , sweets TEXT(50) NULL
  );

INSERT INTO refrigerator (date, fruits, veggies, starches, sweets)
VALUES ("20180220", "melon", "potato", "pasta", "fudge");

REPLACE INTO refrigerator (date, fruits, veggies, starches, sweets)
SELECT
  date
  , COALESCE("apple", fruits) as fruits
  , COALESCE("carrot", veggies) as veggies
  , COALESCE("bread", starches) as starches
  , COALESCE(NULL, sweets) as sweets
 FROM
   refrigerator
 WHERE
   date = "20180220"
UNION ALL
SELECT
  T.date, T.fruits, T.veggies, T.starches, T.sweets
FROM
(
SELECT
  "20180220" as date
  , "apple" as fruits
  , "carrot" as veggies
  , "bread" as starches
  , NULL as sweets
) AS T
WHERE
  NOT EXISTS (SELECT * FROM refrigerator AS R WHERE R.date = T.date);

SELECT date, fruits, veggies, starches, sweets FROM refrigerator;

Here is a SQL Fiddle: Refrigerator SQL Fiddle