Thread: insert only if conditions are met?

Is there a way to insert a record only if a certain<br /> condition is met?<br /><br /> Something like:<br /> insert
intoemployee values('lastname','firstname',8) where<br /> (condition here.. select sum(ofsomething) from xx where
sum(ofsomething)>0)<br /><br /> Is this possible at all with just plain SQL?<br /> 

Henry Ortega <juandelacruz@gmail.com> writes:
> Is there a way to insert a record only if a certain
> condition is met?

> Something like:
> insert into employee values('lastname','firstname',8) where
> (condition here.. select sum(ofsomething) from xx where sum(ofsomething)>0 =
> )

> Is this possible at all with just plain SQL?

Instead of INSERT ... VALUES, use INSERT ... SELECT.
        regards, tom lane

On Wed, Aug 31, 2005 at 11:09:54AM -0400, Henry Ortega wrote:
> Is there a way to insert a record only if a certain
> condition is met?
> 
> Something like:
> insert into employee values('lastname','firstname',8) where
> (condition here.. select sum(ofsomething) from xx where sum(ofsomething)>0 )

See the INSERT documentation; it mentions inserting values from a subquery.

INSERT INTO employee
SELECT 'lastname', 'firstname', 8
WHERE <condition>;

-- 
Michael Fuhr

What I am trying to do is
* Insert a record for EMPLOYEE A to TABLE A
IF
the sum of the hours worked by EMPLOYEE A on TABLE A
is not equal to N

Is this possible?

On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
> What I am trying to do is
> * Insert a record for EMPLOYEE A to TABLE A 
> IF
> the sum of the hours worked by EMPLOYEE A on TABLE A
> is not equal to N
> 
> Is this possible?

Sure, given a suitable schema

It is not clear to me, if the hours worked are
to be found in the same table you want to insert
into, or not.

gnari

Ok. Here's TABLE A

emp            date             hours       type
JSMITH       08-15-2005   5             WORK
JSMITH       08-15-2005   3             WORK
JSMITH       08-25-2005   6             WORK

I want to insert the ff:
1.) JSMITH    08-15-2005    8    VAC
2.) DOE        08-16-2005    8    VAC

#1 should fail because there is already 8 hours entered as being
Worked on 08-15-2005 (same date).

Any suggestions?

SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date =
'8-15-2005'::date will give you the hours. So...

INSERT INTO table   SELECT blah   WHERE (SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date =
'8-15-2005'::date) != 8

Should do what you want.

On Wed, Aug 31, 2005 at 12:49:14PM -0400, Henry Ortega wrote:
> Ok. Here's TABLE A
> 
> emp date hours type
> JSMITH 08-15-2005 5 WORK
> JSMITH 08-15-2005 3 WORK
> JSMITH 08-25-2005 6 WORK
> 
> I want to insert the ff:
> 1.) JSMITH 08-15-2005 8 VAC
> 2.) DOE 08-16-2005 8 VAC
> 
> #1 should fail because there is already 8 hours entered as being
> Worked on 08-15-2005 (same date).
> 
> Any suggestions?
> 
> 
> 
> On 8/31/05, Ragnar Hafsta? <gnari@simnet.is> wrote:
> > 
> > On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
> > > What I am trying to do is
> > > * Insert a record for EMPLOYEE A to TABLE A
> > > IF
> > > the sum of the hours worked by EMPLOYEE A on TABLE A
> > > is not equal to N
> > >
> > > Is this possible?
> > 
> > Sure, given a suitable schema
> > 
> > It is not clear to me, if the hours worked are
> > to be found in the same table you want to insert
> > into, or not.
> > 
> > gnari
> > 
> > 
> > 
> >

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461

On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote:
> Ok. Here's TABLE A
> 
> emp            date             hours       type
> JSMITH       08-15-2005   5             WORK
> JSMITH       08-15-2005   3             WORK
> JSMITH       08-25-2005   6             WORK
> 
> I want to insert the ff:
> 1.) JSMITH    08-15-2005    8    VAC
> 2.) DOE        08-16-2005    8    VAC
> 
> #1 should fail because there is already 8 hours entered as being
> Worked on 08-15-2005 (same date).

sorry, did not notice the duplicates before my previous reply.

you could do something like
insert into A select 'JSMITH','08-15-2005',8,'VAC'   where       8 != (select sum(hours) FROM A
WHEREemp = 'JSMITH'                    AND date = '8-15-2005');
 

gnari

> On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote:
>> Ok. Here's TABLE A
>>
>> emp            date             hours       type
>> JSMITH       08-15-2005   5             WORK
>> JSMITH       08-15-2005   3             WORK
>> JSMITH       08-25-2005   6             WORK
>>
>> I want to insert the ff:
>> 1.) JSMITH    08-15-2005    8    VAC
>> 2.) DOE        08-16-2005    8    VAC
>>
>> #1 should fail because there is already 8 hours entered as being
>> Worked on 08-15-2005 (same date).
>
> sorry, did not notice the duplicates before my previous reply.
>
> you could do something like
> insert into A select 'JSMITH','08-15-2005',8,'VAC'
>    where
>       8 != (select sum(hours) FROM A
>                    WHERE emp = 'JSMITH'
>                    AND date = '8-15-2005');

Wouldn't that fail if JSMITH had only worked 7 hours on 8-15?  I'm 
guessing he'd still want it to fail since adding that 8 hours ov VAC would 
result in a 15 hour day... so maybe something like?

insert into A select 'JSMITH','08-15-2005',8,'VAC'
WHERE
8 >= 8 + (select sum(hours) FROM A                    WHERE emp = 'JSMITH'                    AND date = '8-15-2005');

?

On Wed, 2005-08-31 at 14:54, Jim C. Nasby wrote:
> SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date =
> '8-15-2005'::date will give you the hours. So...
> 
> INSERT INTO table
>     SELECT blah
>     WHERE (SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date =
> '8-15-2005'::date) != 8
> 
> Should do what you want.

I keep thinking a constraint it a better idea here.  Or is this too
complex to make it one easily.

Either way, I'd make that last part:

<8 instead of !=8, in case this uses floating point instead of int, and
gets 8.0000000001 or what not in it.  Or if someone manages to get it up
to 9 or something.

Henry Ortega wrote:
> Ok. Here's TABLE A
>
> emp            date             hours       type
> JSMITH       08-15-2005   5             WORK
> JSMITH       08-15-2005   3             WORK
> JSMITH       08-25-2005   6             WORK
>
> I want to insert the ff:
> 1.) JSMITH    08-15-2005    8    VAC
> 2.) DOE        08-16-2005    8    VAC
>
> #1 should fail because there is already 8 hours entered as being
> Worked on 08-15-2005 (same date).
>
> Any suggestions?

CREATE TABLE "tablea" ("emp"      varchar(6) NOT NULL,"date"     varchar(10) NOT NULL,"hours"    int NOT NULL,"type"
char(4) NOT NULL); 

grant select, insert, update, delete on tablea to public;

insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-15-2005', 5, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-15-2005', 3, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-25-2005', 6, 'WORK' );

create or replace function overtime( varchar, varchar, int, varchar )
returns void as '   insert into tablea( emp, date, hours, type )      select $1, $2, $3, $4      from tablea where (
selectsum( hours ) from tablea where emp =  
$1 and date = $2 group by emp, date ) + $3 <= 8      union      select $1, $2, $3, $4      from tablea where( select
sum(hours ) from tablea where emp = $1  
and date = $2 group by emp, date ) is null
' LANGUAGE SQL;

select overtime( 'JSMITH', '08-15-2005', 8, 'VAC' );    # REJECTED
select overtime( 'JSMITH', '08-16-2005', 8, 'VAC' );    # OK
select overtime( 'JSMITH', '08-25-2005', 2, 'WORK' );    # OK

select * from tablea;

>
>
>
>
> On 8/31/05, Ragnar Hafstað < gnari@simnet.is <mailto:gnari@simnet.is> >
> wrote:
>
> On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
>
>>What I am trying to do is
>>* Insert a record for EMPLOYEE A to TABLE A
>>IF
>>the sum of the hours worked by EMPLOYEE A on TABLE A
>>is not equal to N
>>
>>Is this possible?
>
>
> Sure, given a suitable schema
>
> It is not clear to me, if the hours worked are
> to be found in the same table you want to insert
> into, or not.
>
> gnari
>
>
>
>
>
>
>

--
Daryl Richter
Platform Author & Director of Technology
v: 610.361.1000 x202

((         Brandywine Asset Management          ) ( "Expanding the Science of Global Investing"  ) (
http://www.brandywine.com          )) 

Thanks for all your answers. Very helpful.
What if after adding all those hours in one long transaction,
I want to send a query to check the MONTHLY TOTAL HOURS
(including those just entered)
and if they exceed N number of hours, all those records added
should *ROLLBACK*?

BEGIN;
insert..........
insert.........
  if sum(hours)>N then ROLLBACK
END;

Is that possible? Maybe with just plain SQL? (and one transaction)

On Fri, 2 Sep 2005, Henry Ortega wrote:

> Thanks for all your answers. Very helpful.
> What if after adding all those hours in one long transaction,
> I want to send a query to check the MONTHLY TOTAL HOURS
> (including those just entered)
> and if they exceed N number of hours, all those records added
> should *ROLLBACK*?
>
> BEGIN;
> insert..........
> insert.........
> if sum(hours)>N then ROLLBACK
> END;
>
> Is that possible? Maybe with just plain SQL? (and one transaction)
>

Just add in another where clause using AND and modify the values to sum 
the hours for the entire month instead of just the day.

At least I think that would do it.



>
>
> On 8/31/05, Philip Hallstrom <postgresql@philip.pjkh.com> wrote:
>>
>>> On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote:
>>>> Ok. Here's TABLE A
>>>>
>>>> emp date hours type
>>>> JSMITH 08-15-2005 5 WORK
>>>> JSMITH 08-15-2005 3 WORK
>>>> JSMITH 08-25-2005 6 WORK
>>>>
>>>> I want to insert the ff:
>>>> 1.) JSMITH 08-15-2005 8 VAC
>>>> 2.) DOE 08-16-2005 8 VAC
>>>>
>>>> #1 should fail because there is already 8 hours entered as being
>>>> Worked on 08-15-2005 (same date).
>>>
>>> sorry, did not notice the duplicates before my previous reply.
>>>
>>> you could do something like
>>> insert into A select 'JSMITH','08-15-2005',8,'VAC'
>>> where
>>> 8 != (select sum(hours) FROM A
>>> WHERE emp = 'JSMITH'
>>> AND date = '8-15-2005');
>>
>> Wouldn't that fail if JSMITH had only worked 7 hours on 8-15? I'm
>> guessing he'd still want it to fail since adding that 8 hours ov VAC would
>> result in a 15 hour day... so maybe something like?
>>
>> insert into A select 'JSMITH','08-15-2005',8,'VAC'
>> WHERE
>> 8 >= 8 + (select sum(hours) FROM A
>> WHERE emp = 'JSMITH'
>> AND date = '8-15-2005');
>>
>> ?
>>
>

Henry Ortega wrote:

> What I am trying to do is
> * Insert a record for EMPLOYEE A to TABLE A
> IF
> the sum of the hours worked by EMPLOYEE A on TABLE A
> is not equal to N
>
> Is this possible?

Yes, but we will need to see your database schema to provide examples.

A simple example might be
INSERT INTO table_a
SELECT firstname, lastname FROM table_b
WHERE table_b IN (SELECT id FROM (SELECT id, sum(labor) as total_hours
from table_c group by id) WHERE total_hours <> n);

Best Wishes,
Chris Travers
Metatron Technology Consulting