This topic is locked

How to update table from Excel Import without Primary Field Value

10/29/2015 10:26:58 AM
PHPRunner General questions
A
Abul author

My table is VARIETY where:

  1. Var_id INT PK NOTNULL AI
  2. Var_Code VARCHAR UNIQUE NOTNULL
  3. Var_name VARCHAR
  4. Var_note TEXT
    If I import excel file with Var_id field empty then all records added to the table as new records. If my excel file has Var_id field filled then the specific records are updated. But I want this var_id field on my excel file should be left empty always but Var_Code field should be filled (NOT NULL). When I import this file, I want the application should check Var_code field first and do either new insert (in case mismatch) or update old record (in case duplicate). Could anyone please give me idea how can I accomplish this? Thanks.

romaldus 11/3/2015



My table is VARIETY where:

  1. Var_id INT PK NOTNULL AI
  2. Var_Code VARCHAR UNIQUE NOTNULL
  3. Var_name VARCHAR
  4. Var_note TEXT
    If I import excel file with Var_id field empty then all records added to the table as new records. If my excel file has Var_id field filled then the specific records are updated. But I want this var_id field on my excel file should be left empty always but Var_Code field should be filled (NOT NULL). When I import this file, I want the application should check Var_code field first and do either new insert (in case mismatch) or update old record (in case duplicate). Could anyone please give me idea how can I accomplish this? Thanks.


In VARIETY table, create a unique index for VAR_ID and VAR_CODE field :



ALTER TABLE `variety`

ADD UNIQUE INDEX `any_name_you_want` (`var_id`, `var_code`);


Since VAR_ID field is Autoincrement field, leave it empty on excel

romaldus 11/3/2015

one more question from me. Since the var_code field is unique, why don't you make it as primary key?