multiple rows update or insert mysql

Question

I've created a table like this,

   id   |   option_name   |   value   |   user_id
----------------------------------------------
   1    |   name          |  Joe      |   1
----------------------------------------------
   2    |   age           |   30      |   1
----------------------------------------------
   3    |   sex           |   male    |   1
----------------------------------------------
   4    |   name          |  Jane     |   2
----------------------------------------------
   5    |   age           |   28      |   2
----------------------------------------------
   6    |   sex           |  female   |   2
----------------------------------------------

I want to update all rows corresponding of user_id and option_name.

If user_id == 3, when i submit form with option_name (name,sex,age) as fields, if there is no rows with user_id == 3 then insert rows but if rows exist i want to update those row with new values for value field.

Please check my code: http://pastebin.com/THQdYpix

I want to reduce query steps in my code, any idea?

And your specific question about that task is? We won't do it all for you. — juergen d, Feb 10 '15 at 06:02
possible duplicate of [MySql: if value exists UPDATE else INSERT](http://stackoverflow.com/questions/10095680/mysql-if-value-exists-update-else-insert) — Gunaseelan, Feb 11 '15 at 15:17

score 0 · Answer 1 · answered Feb 10 '15 at 06:06

0

First check if it updates or note if the mysql_query() returns false then you can use insert query and execute the query.

answered Feb 10 '15 at 06:06

Shoeb Ansari

17
2

score 0 · Answer 2 · answered Feb 10 '15 at 06:06

Use INSERT ... ON DUPLICATE KEY UPDATE.If you want to do this in a single statement, I would recommend using the INSERT ... ON DUPLICATE KEY UPDATE syntax, as follows:

INSERT INTO table (id, someothervalue) VALUES (1, 'hi mom') ON DUPLICATE KEY UPDATE someothervalue = 'hi mom';

The initial INSERT statement will execute if there is no existing record with the specified key value (either primary key or unique). If a record already exists, the following UPDATE statement (someothervalue = 3) is executed.

@user3643568this may help you – Priyank Feb 10 '15 at 06:08 — Priyank, Feb 10 '15 at 06:08

score 0 · Answer 3 · answered Feb 11 '15 at 15:21

0

Below query will work.

REPLACE INTO `table1`
( `name`,`sex`,`age` )
VALUES
( 'Mark', '29', 'male' )
ON DUPLICATE KEY UPDATE user_id=3;

answered Feb 11 '15 at 15:21

Gunaseelan

2,494
5
36
43

multiple rows update or insert mysql

3 Answers3