Insert or Update depending on some condition in MySQL

Question

After reading MySQL documentation I have not found the answer to my question! Let me explain what I am trying to do:

I want to insert a new row to my table. If some column of the row I would like to insert already exists in another row of the table, it must be replaced. Here is my CURRENT MySQL sentence:

INSERT INTO responses (ref_user_id, ref_proposal_id, user_name, response)
SELECT user_id, proposal_id, '$response_name', '$response_response_type'
FROM users, proposals
WHERE private_user_id = 'some_input_data_1'
  AND public_proposal_id = '$some_input_data_2'

Concretely, if any row contains the same 'ref_user_id' and 'ref_proposal_id', it must be replaced. Thanks in advance,

PS: I guess the solution is some kind of INSERT or UPDATE but I didn't find the way.

Answer 1

all you need is to use INSERT..ON DUPLICATE KEY UPDATE

before anything else, you need to create a unique constraint for both column to make it working,

ALTER TABLE responses ADD CONSTRAINT uq_col UNIQUE (ref_user_id, ref_proposal_id)

and execute the following statement

INSERT INTO responses (ref_user_id, ref_proposal_id, user_name, response)
SELECT user_id, proposal_id, '$response_name', '$response_response_type'
FROM   users, proposals
WHERE  private_user_id = 'some_input_data_1' AND 
       public_proposal_id = '$some_input_data_2'
ON DUPLICATE KEY UPDATE user_name = '$response_name',
                        response = '$response_response_type'

as a sidenote, please fix the way you join the tables, it returns cartesian product which you might not want.