MySql Update field or Insert, if it does not exist, there is no primary key

Question

I have a table with three columns (all numeric) with fields CustID (not unique), ProdID (not unique) and Price. The only “uniqueness” is that no CustID / ProdID pair is duplicated. Thus, each customer has different "prices" for each product. I use the three-line method. (a) find if the pair exists, (b) if so, I update the price, (c) if not, I insert a new pair with the price. I have not created indexes for id yet (it works fine for dev purposes.)

Can anyone suggest a way to use the REPLACE statement, most INSERT / ON DUPE examples seem to imply that a primary key is needed. Usually I always have pk, it just seemed wasteful for this requirement. Many thanks to everyone who will help me over time. Ken Ashton

Sorry Michael, you already gave me an example. Now I will find out the voices, thank you very much guys

Answer 1

As suggested by Michael and Tony, PCs are never wasted. Therefore, it is always worth having a PC in every table. Therefore, I first proposed to create a PC, and then use the INSERT ON DUPLICATE KEY.

To achieve what you want without a PC, you can first execute DELETE and then INSERT. This is actually what REPLACE does inside.