-1

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?

Improve this question
0

1 Answer 1

Reset to default
1

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", "[email protected]", "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";

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Isn't that going to match only if the client id field is the same (the primary key)? How can I change into ON DUPLICATE email UPDATE...
No, if you have a unique index on the email field and not the name, phone and address fields, it will only insert if the email is missing and update if it is present. You do not include the id (primary key) because with autoincrement it is inserted automatically when there is no record and not touched if there is a record with the email address.
Am I missing something? It didn't work. I tried to update a record that had the same email address as the new values and it just created a new record with the same address... It looked like this the result: 129 Jok Test [email protected] 1186 John Doe [email protected] Email has unique index.
@HarrySid then despite all appearances, the email value is not a duplicate of an exsisting one
My mistake.... The email field had an index but it was not set with the unique restrain.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.