INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Insert without duplicates ?

Insert without duplicates ?

(OP)
How to insert a record making sure there's no duplicate ?
 
INSERT INTO invoices (invoiceid, billed) VALUES ('12345', 'TRUE')
WHERE NOT EXISTS (SELECT * FROM invoices WHERE invoices.invoiceid = '12345')

I keeps getting an error message and can't figure out how to do this ?
Syntax error at or near "WHERE"

Much TIA's
 

RE: Insert without duplicates ?

(OP)
Or this variation on the same theme

IF EXISTS (SELECT * FROM invoices WHERE invoiceid = '12345')
UPDATE invoices SET billed = 'TRUE' WHERE invoiceid = '12345'
ELSE
INSERT INTO invoices (invoiceid, billed) VALUES ('12345', 'TRUE')
END IF

sad

RE: Insert without duplicates ?

Hi

Maybe :

CODE

insert into invoices (invoiceid,billed)
select '12345','true' where not exists (select 1 from invoices where invoiceid='12345')
Regarding the second form, needs a stored procedure :

CODE

create or replace function putinvoice(varchar(10),varchar(5))
returns boolean as '
declare
  _invoiceid alias for $1;
  _billed alias for $2;
begin
  update invoices set billed=_billed where invoiceid=_invoiceid;
  if not found then
    insert into invoices (invoiceid,billed) values (_invoiceid,_billed);
  end if;
  return true;
end;
' language plpgsql;

Feherke.
http://rootshell.be/~feherke/

RE: Insert without duplicates ?

(OP)
Still trying to get this working.

CODE

DROP TABLE invoices;
CREATE TABLE invoices (
    invoiceid VARCHAR(5),
    billed BOOLEAN,
    CONSTRAINT idkey PRIMARY KEY (invoiceid)
);
INSERT INTO invoices (invoiceid, billed)
VALUES ('12345', 'true') WHERE NOT EXIST (SELECT 1 FROM INVOICES WHERE invoiceid = '12345');

ERROR: syntax error at or near "WHERE"
SQL state: 42601
Character: 213

Grateful for any more clues.
Hoping to keep this simple rather than use stored procedure.
 

RE: Insert without duplicates ?

(OP)
Sorry, I meant SELECT not VALUES

CODE

INSERT INTO invoices (invoiceid, billed)
SELECT ('12345', 'true') WHERE NOT EXIST (SELECT 1 FROM INVOICES WHERE invoiceid = '12345');
throws error as above.
TIA's for clues

RE: Insert without duplicates ?

(OP)
Ok, finally got it.
For completeness, and maybe to help anybuddy else who hits this problem, this seems to work reliably.
Hope others can avoid repeating my mistake, just make new ones.

CODE

insert into invoices (invoiceid, billed) (select '12345' as invoiceid, 'TRUE' as billed where
not exists (select 1 from invoices where invoiceid='12345'));
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close