CODEWITHSUNDEEP
mysqlsql
Satya
Satya

Reputation: 358

Mysql query syntax for conditional inserts

In following example I want to insert in table only if there is no data otherwise it is giving PK violation. How to do conditional insert in mysql. Please note I am using mysql db.

create table if not exists visa_amt_conversion (
  last_char char(1) not null,
  last_digit char(1) not null,
  tran_sign char(1) not null,
  primary key (last_char)
 );

insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('{', '0', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('A', '1', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('B', '2', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('C', '3', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('D', '4', '+'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('E', '5', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('F', '6', '+'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('G', '7', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('H', '8', '+'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('I', '9', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('}', '0', '-'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('J', '1', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('K', '2', '-'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('L', '3', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('M', '4', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('N', '5', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('O', '6', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('P', '7', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('Q', '8', '-'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('R', '9', '-');

Upvotes: 0

Views: 31

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The standard way to do this is something like:

insert into visa_amt_conversion (last_char, last_digit, tran_sign)
    VALUES ('{', '0', '+')
    on duplicate key update last_char = values(last_char);

If an attempt is made to insert values that violate a unique or primary key constraint, then the row is updated instead. The last_char = values(last_char)) is a no-op. Because the value doesn't change, the row isn't even updated.

The shorter syntax:

insert ignore into visa_amt_conversion (last_char, last_digit, tran_sign)
    VALUES ('{', '0', '+');

does the same thing in this case. However, it is not recommended because it ignores all errors. It is better to use the first version, because it ignores only the specific error that you want to ignore.

Upvotes: 3

alseether
alseether

Reputation: 1993

What about

select count(last_char) from visa_amt_conversion;

If that query returns 0, table is empty. If you get an error, table doesn't exists.

Upvotes: 0

Related Questions