I have a somewhat long MySQL ETL statement where I am inserting 32 columns of data into a table. This ETL statement is also utilizing UNION as a derived table.
My code is similar to this, as defined in the the MySQL reference manual:
INSERT INTO t1 (a, b, ... "32 columns total")
SELECT * FROM
(SELECT a, b, ... FROM t2
LEFT JOIN t3 on t2.id = t3.id
UNION
SELECT a, b, ... FROM t2
RIGHT JOIN t3 on t2.id = t3.id
) AS dt
**ON DUPLICATE KEY UPDATE b = b + c**;
The problem: When using 'On Duplicate Key Update' I am getting duplicate rows added to my destination table. I suspect I don't have this last line of the code written correctly.
My question: Do I need to add ALL of the 32 columns from my table to this 'On Duplicate Key Update' statement? Or ONLY columns with data that might change?
Thanks in advance, I find this part of the MySQL reference manual confusing and their examples much more simplistic than my real-world scenario. I've searched and could not find an answer or example that is similar to my situation.
Thanks @Barmar, I appreciate the dialog!
Turns out that since I was trying to avoid this bug: https://bugs.mysql.com/bug.php?id=58637
I tricked myself into creating a primary key that did not work as intended. ;) I had created a composite primary index which was composed of an (auto-increment number, r_id, t_id). Since the auto-increment number was not part of my insert it was allowing the rows to duplicate.
I changed my table indexes so the Primary index is still the auto-increment number and there is a new Unique index which includes (r_id and t_id). This seems to be working now.
I am still curious about part of my original question: When creating the 'on duplicate key update' statement ... Do I really need to include each field that could change? In other words ... Does the statement only evaluate the fields in this statement for changes?
Thanks again @Barmar!
This is my first ever question posted to stackoverflow so hopefully I followed protocol!