UPDATE, INSERT and ON DUPLICATE KEY UPDATE in one query

Question

I am new to posting on Stack Overflow, so please bare with me.

Say I have a table called Table1 with the columns order_id and general_notes (and a few others with more data).

I am trying to write a query that 1) INSERT order_id and general_notes INTO my table if order_id does not exist yet 2) CONTACT UPDATE general_notes if column already contains text and 3)UPDATE general_notes column if order_id has been pushed to db already. Essentially I do not want order_id to be duplicated in my table so if it doesn't exist I want to push both columns, if it already exists without any general_notes added I want to add data and if general_notes does already exist I want to concat new text to the end.

The script I currently have adds new entry to db and updates the general_notes column if additional notes are added but I can't figure out how to update the general_notes column for an order_id that is already in db aka general_notes is NULL.

This is what I currently have:

$query = "INSERT INTO table1 (order_id, general_notes) VALUES(:order_id, :general_notes) ON DUPLICATE KEY UPDATE general_notes = CONCAT(general_notes, '. ', :general_notes)"; 

Answer 1

I would recommend concat_ws(): it gracefully skips null values (and does not concatenates the separator to nulls):

INSERT INTO table1 (order_id, general_notes) 
VALUES(:order_id, :general_notes) 
ON DUPLICATE KEY UPDATE 
    general_notes = CONCAT_WS('. ', general_notes, VALUES(general_notes))

Note that you can use VALUES() in the ON DUPLICATE KEY clause to refer to the value that would have been otherwise inserted.