0 votes
1 view
in SQL by (20.3k points)

I have a SQL query where I want to insert multiple rows in a single query. so I used something like:

$sql = "INSERT INTO beautiful (name, age)

  VALUES

  ('Helen', 24),

  ('Katrina', 21),

  ('Samia', 22),

  ('Hui Ling', 25),

  ('Yumie', 29)";

mysql_query( $sql, $conn );

The problem is when I execute this query, I want to check whether a UNIQUE key (which is not the PRIMARY KEY), e.g. 'name' above, should be checked and if such a 'name' already exists, the corresponding whole row should be updated otherwise inserted.

For instance, in the example below, if 'Katrina' is already present in the database, the whole row, irrespective of the number of fields, should be updated. Again if 'Samia' is not present, the row should be inserted.

I thought of using:

INSERT INTO beautiful (name, age)

      VALUES

      ('Helen', 24),

      ('Katrina', 21),

      ('Samia', 22),

      ('Hui Ling', 25),

      ('Yumie', 29) ON DUPLICATE KEY UPDATE

Here is the trap. I got stuck and confused about how to proceed. I have multiple rows to insert/update at a time. Please give me direction. Thanks.

1 Answer

0 votes
by (40.3k points)

You can use keyword 'VALUES' to refer to the new values.

INSERT INTO beautiful (name, age)

    VALUES

    ('Helen', 24),

    ('Katrina', 21),

    ('Samia', 22),

    ('Hui Ling', 25),

    ('Yumie', 29)

ON DUPLICATE KEY UPDATE

    age = VALUES(age),

     ...

For more information, you can refer to  documentation

Related questions

0 votes
1 answer
asked Jul 4, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
asked Jul 12, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...