how do I write an SQL INSERT query that will avoid duplicating data that are in a range on a row

Question

 id | user_id | job_range | start_date | client_name| job_type | job_no     |  job_quan                        
 1  |    4    | 1-3000    | 2014-05-13 |  kenny's   | coloured |   t50000   |    n4500
 2  |    2    | 3001-4500 | 2014-05-13 |  kenny's   | coloured |   t50000   |    n4500
 3  |    3    | 1-2000    | 2014-05-15 |  fredy's   | plain    |   t42100   |    n5000
 4  |    4    |2001-5000  | 2014-05-15 |  fredy's   | plain    |   t42100   |    n5000 

I want to write a query that uses range of numbers already inputted into the database (job_range) and matches it with a job_no to avoid another user from putting jobs in the job range into the database. the number cannot be within the lower range already given. for any specific job, if there is a range of 1-2000 then you cannot add another row to the table for that job with the range starting between the 1-2000, only jobs from 2001 to 5000 can be picked. so user can only chose to do jobs btw range 2001 -5000

Answer 1

You have to make the column UNIQUE. You can do that by running this query (you'll need to delete duplicate entries first):

ALTER TABLE jobs ADD UNIQUE (desc)

Then when you try to run a query and the desc column already exists, it will return an error.

Answer 2

CREATE TABLE test_dup (id number, dup_from number,dup_to number);

create table dup_val_range (range_values NUMBER);

 ALTER TABLE dup_val_range
ADD CONSTRAINT rng_unique unique (range_values);

CREATE SEQUENCE rng_seq
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 9999999999999999999
NOCYCLE
NOCACHE
NOORDER;

create or replace procedure ins_rng(p_from IN NUMBER, p_to IN NUMBER)
AS
v_indx1 NUMBER;
BEGIN
IF p_from> p_to THEN

RAISE_APPLICATION_ERROR(-20001, 'from is Higher tham To');

END IF;

-- check for nulls

FOR i IN p_from..p_to
LOOP

    BEGIN
    insert into dup_val_range (range_values)
    values (i);
    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
    rollback;
    RAISE_APPLICATION_ERROR(-20001, 'Range ir taken!');

    END;

END LOOP;

insert into test_dup
values (rng_seq.nextval,p_from,p_to);

END;


exec ins_rng(p_from=>1, p_to=>19);

exec ins_rng(p_from=>5, p_to=>20);

ORA-20001: Range ir taken!

    select * from dup_val_range 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19


    select * from test_dup
3   1   19

But, if values wont be inserted through this proc ir will be added with hand this wont help..

Answer 3

As Marcell says, it isn't possible to use WHERE on an INSERT.

I'd be inclined to set a UNIQUE key on 'desc' based on your table layout and desired action, and then do a standard INSERT. If 1-300 has been inserted already, then the INSERT will fail with a duplicate key error that you can catch in your application.

ALTER TABLE `jobs` ADD UNIQUE (desc);

Answer 4

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Here is the more detailed explanation and usage of ON DUPLICATE KEY

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer 5

one way you needed to split column (desc to desc_from and desc_to) or(substr string to get value from ,to) and you need check that from can't be greater than to and could use something like this..

CREATE TABLE test_dup (id number, dup_from number,dup_to number);

select * from test_dup;

 ALTER TABLE test_dup
ADD CONSTRAINT chk_dup CHECK (dup_from<=dup_to);

insert into test_dup
select 1,2,300 from dual where not exists (select 1 from test_dup where 2<=dup_to AND 300>=dup_from);

insert into test_dup
select 2,4,399 from dual where not exists (select 1 from test_dup where 4<=dup_to AND 399>=dup_from);

insert into test_dup
select 3,1,500 from dual where not exists (select 1 from test_dup where 1<=dup_to AND 500>=dup_from);

insert into test_dup
select 4,301,304 from dual where not exists (select 1 from test_dup where 301<=dup_to AND 304>=dup_from);

insert into test_dup
select 4,200,200 from dual where not exists (select 1 from test_dup where 200<=dup_to AND 200>=dup_from);

insert into test_dup
select 4,555,555 from dual where not exists (select 1 from test_dup where 555<=dup_to AND 555>=dup_from);


select * from test_dup;

1   2   300
4   301 304
4   555 555

else ?you could use function based constraint where you put in same check, or not? or one more you could create new check table and add unique counstraint. before you insert on real table you put all range of data into check table later catch error on dupval if there is

insert into test_dup
select 1,2,300 from dual where not exists (select 1 from test_dup where 2<=dup_to AND 300>=dup_from AND dup_from<dup_to);

one more

create or replace procedure insert_rng (p_id IN NUMBER, p_from IN NUMBER, p_to IN NUMBER)
AS
BEGIN

insert into test_dup
select p_id,p_from,p_to from dual where not exists (select 1 from test_dup where p_from<=dup_to AND p_to>=dup_from AND dup_from<=dup_to);
 IF sql%notfound THEN
RAISE_APPLICATION_ERROR(-20001, 'range taken!');
END IF;

END;

exec insert_rng (p_id=>1, p_from=>1, p_to=>99);

Answer 6

You want your INSERT to fail if some condition on the two values in your desc field isn't met.

First, as user2879235 mentions, the condition would be much easier to formulate if the desc field were split into two separate columns, let's call them start and end.

Another issue is with the meaning of the values in desc. Sometimes you only have 1 value, sometimes you have 2. Is 1000 just a shorthand for 1000-1000 or is it something else? I will assume that it is. And do 100-1000 and 1000-1100 conflict or do they not? I will assume that they do.

With these assumptions, I think your constraint can be formulated as (please verify this!)

NOT EXISTS (
    SELECT j FROM jobs WHERE (j.start <= new.start AND j.end >= new.start)
                          OR (j.start <= new.end AND j.end >= new.end)
)

where new is the newly created tuple.

You want this constraint to be checked upon trying to insert, but MySQL doesn't support checking CHECK constraints, so you'll need to use a trigger instead in which you can raise a SIGNAL when the opposite of this constraint is met, which should roll back (i.e. undo) the INSERT.