SQL Question

[eeboy]

Assume I have a table of parts for a particular job. Parts are added to the job as they are needed. Some times a given part is needed more than once so I could potentially have multiple parts show up in the table. To avoid this I'd rather just have a quantity field so that would increment instead.

Is there a way to make an INSERT statement add a part if it doesn't already exist OR increment the quantity if it does?

Thanks!

[ahoodin]

One option is have all parts already in the database with a zero quantity.

Then just do an UPDATE with the new quantity.

Or you will have to do a SELECT and if the number of records returned is 1, do an UPDATE otherwise do the INSERT.

HTH,

[DanielaTm]

Hi, I agree with ahoodin having all the records in database with 0 quantity. It is more performant that counting each time

[f_eriksen]

Hi,

im ms sql, it could also look like:

-- check for part
if not exists (select 1 from mytable where mypk = 'mypk')
begin
-- we dont have this row, create it
insert into mytable (col1, col2, col3) values ('val1','val2', 'val3')
end
else
begin
-- its already there, update qty
update mytable set col1 = col1+1 where mypk = 'mypk'
end


it could also be done in 2 separate stmts, using insert into... select ... where not exists... followed by an update...set qty=qty+1 where ....


HTH,

Fridthjof