Question 358711 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I add the same record in MySQL

How to add the same record in MySQL, like the same Lastname only.

create table Person
(Lastname varchar(15) primary key,
Firstname varchar(15),
Address varchar(30),
Province varchar(30));

insert into Persons
    values
    ('Soriano', 'MamaMo', 'Sa tabi-tabi', 'Pangasinan');

ERROR 1062 (23000): Duplicate entry ‘Soriano’ for key ‘PRIMARY’

……

Tags:

2

Answers


  1. 0

    Duplicate data may result by adding the exact same record more than once in MySQL, which is typically not desired. Depending on what you want to do, you can use one of these two methods to get there:

    1. Duplicate handling during insertion:

    INSERT… ON UPDATE DUPLICATE KEY: This method makes an attempt to add a new record. The current row is updated with the new values mentioned in the UPDATE clause in the event of a duplicate key violation, which happens when the same value appears in a main or unique key column.

    1. Adding an updated record:

    Make a new record with somewhat changed information. This may be accomplished by:
    modifying certain values by hand: Before inserting, change a value in the record (e.g., add a suffix to a name).
    Making use of a function While inserting, apply a function to a value (e.g., increment a counter).

    Data inconsistencies and retrieval problems might result from duplicates.
    Examine your unique requirements to ascertain the best course of action.

    Login or Signup to reply.
  2. 0

    Try adding a Primary Key to your table like this instead :

        CREATE TABLE Person (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Lastname VARCHAR(15),
    Firstname VARCHAR(15),
    Address VARCHAR(30),
    Province VARCHAR(30)
);

    Then your insert would look like this :

    INSERT INTO Persons (ID,Lastname,Firstname,Address,Province) VALUES (NULL,'Soriano', 'MamaMo', 'Sa tabi-tabi', 'Pangasinan')

    We want to specify the (column names ) VALUES (column values) so that they insert exactly in the columns we need them, although it is possible to leave the (column names) part off, its best to specify if you plan on making changes to the table later.

    As mentioned in the comments, you really want an Integer based Primary Key as it gives a unique value that increases automatically in which to directly select a record. If you did not do this, you will not be sure which person is selected when you pull the data and might update the incorrect record if there are duplicate values you are searching for such as a common last name. Thus, when you SELECT them later, do it by the id = # so you can get the exact record you want.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search