Update/ Upsert records in a mySQL database strategy when not using the primary key

I am new to databases and I am facing a challenge. I am connecting an ordering system to a a db with a client and order table like the following:

Client.	Order.
id (primary key)	order_id (primary key)
name	amount
email	email Foreign key
phone	system_order_id
address	-

I have the following situation that I don't know how to handle: I get an order from our order system that has the following information:

system_order_id
amount
email
phone
name

I want to check if the client has a record in our DB. If not, add a row in the DB If yes, update the client row matched on email not the primary key, with extra info (the phone number)

How can I do that easily? How can I

REPLACE INTO

maching with email, not the primary key and updating only the info that I am missing. i.e. update the phone number but keep the address and name from the record.

Is there a better way to do it? Finally is ther a better way to structure my DB?

Solution

I have done this type of thing in MySQL/PHP. Your id primary key must be autoincrement and you will need a unique index on the email field.

INSERT INTO Client (name, email, phone, address) VALUES ("Client Name", "client@email.com", "555-555-5555", "000 Street Address, City, State, Zip") ON DUPLICATE KEY UPDATE name = "Client Name", phone = "555-555-5555", address = "000 Street Address, City, State, Zip";