MySQL Query: UPDATE and/or APPEND

Question

I have a temporary table that I use to insert into the master db.

The temp table is named "temp_table" The master table is "master"

I currently use the following command to update "master"

SELECT COUNT(*) FROM master;
SHOW COLUMNS FROM master;
INSERT INTO master
SELECT * FROM temp_table
ON DUPLICATE KEY UPDATE email = VALUES(email), phone = VALUES(phone)

Now, I want to be able to append field (counter) from the "temp table" into "master." The field already exists in both tables and I just want to be able to update or append it.

"counter" field in master may be empty or it may contain a number value already.

In cases where the value exists, it should append separated by a comma.  Format (88,89,90)
In cases where the it's empty, it should update (88)

Thank you in advance.

What you call append is also an update. However, you really should think twice before storing a delimited list of values in a single field value. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad for details why this is not such a good idea — Shadow, Jul 29 '20 at 21:47

score 0 · Accepted Answer · answered Jul 29 '20 at 22:02

I think you want:

on duplicate key update 
    email = values(email), 
    phone = values(phone),
    counter = case when counter is null 
        then values(counter) 
        else concat(counter, ',', values(counter))
    end

You can also phrase this with coalesce(), although the expression might be a bit more complicated to understand:

on duplicate key update 
    email = values(email), 
    phone = values(phone),
    counter = concat(
        coalesce(concat(counter, ','), ''),
        values(counter)
    )

Thank you for your help, this is nice and clear. I ran it and i'm getting ambiguous error with "counter" field. — mysql_user, Jul 30 '20 at 19:18

MySQL Query: UPDATE and/or APPEND

1 Answers1