I have a table with a primary key which is two integer fields, each a foreign key to a different table. (Multi to Multi table)
In one of the foreign tables two rows are merged and this table is updated to match the changes which can result in two rows being duplicates and cause the query to fail.
There has to be a better way to do the following: (which does work)
SELECT row1, row2 FROM table WHERE row2 = @ID OR FK_Gmb_row2 = @ID2 GROUP BY row1, row2; DELETE FROM table WHERE row1 = @ID OR row2 = @ID2; -- process select in code and loop over results:: INSERT INTO table (row1, row2) VALUES (@val1, @val2)
So if FK_row had the values 2 and 3 merged Table 1 would go from
FK_row1 , FK_row2 ----------------- 1 , 1 1 , 2 1 , 3 2 , 3
FK_row1 , FK_row2 ----------------- 1 , 1 1 , 3 2 , 3
I feel like I am using this solution because I do not properly understand something about the database system.