2

I am inserting data into a table looks like this

|   num   | name     |  value |
----------------------------------
|    1    | name1    |   1    |
|    2    | name2    |   1    |
|    3    | name3    |   1    |
|    4    | name4    |   2    |
|    5    | name5    |   3    |

I wanted to insert with where clause like insert into table (num, name, value) values(6,name,1) when (num and value together) not exist in any row together

I tried to select first and insert on basis of that result but I think that is not the best way I want it in a single query

tried like: select * from the table where name=$name and value= $value if I got result then not insert otherwise insert. It was done with two queries but i don't want it.

Any help will be appriciated.

bala
  • 125
  • 6

2 Answers2

4

Use a unique constraint to enforce uniqueness for (num, value):

alter table t add constraint unq_t_num_value unique (num, value);

Then the database ensures that the integrity of the table -- that these values are unique. You don't have to do it explicitly.

Note that if the unique constraint is violated, you get an error and the insert is aborted (along with other rows that might be inserted). If you want to ignore the error instead, you can use on conflict ignore.

Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
  • what if try to insert and violate there uniqueness. other rows which have not unique value will be inserted or not and if there will be an error how can I get inserted id i wanted a simple query without making uniqueness can you please tell me @Gordon Linoff – bala Apr 15 '20 at 14:12
  • @bala . . . As pointed out in the answer, you can use `on conflict` to ignore errors. – Gordon Linoff Apr 15 '20 at 14:23
  • there is a problem .... autoincrement incremented either it inserted or not. I mean on the conflict in not inserting but autoincrement increases @Gordon Linoff – bala Apr 15 '20 at 15:01
-1

Basically, first you need to do check if record with same num and Value is already present in table or not. if it is present then don't insert else insert the new record.

for this you can try to insert value with procedure:

the below procedure will help based on your need:

create procedure InsertRecVali(@num int,@name varchar(max),@value int)

as 

begin

if not exist(select 1 from table where num=@num and value=@value)

insert into table values(@num,@name,@value)

Else 

PRINT 'Cannot Insert Duplicate Value'

End;

After creating procedure, execute this procedure by passing the the values which you want to insert in table.

so before inserting record in table, it will check if there is already record present in table with same num and value then it will not insert it and give error 'Cannot Insert duplicate Value'.

otherwise if the record with same num and value is not already present in table then only it will insert record in table.

below is the example to execute this procedure:

EXEC dbo.InsertRecVali(1,'abc',6)
Developer_Suraj
  • 10
  • 2
  • Apart from the fact that this is supposed to be done with a [unique index](https://stackoverflow.com/a/61230055/11683), this particular stored procedure is a race condition and will result in [duplicate values](https://stackoverflow.com/q/3407857/11683) inserted under high load. That is, it would, if it was SQL Server, but the OP is asking about PostgreSQL where this syntax won't even work. – GSerg Apr 23 '20 at 06:47