CODEWITHSUNDEEP
postgresqlwhere-clause
Himaja Sidhireddi
Himaja Sidhireddi

Reputation: 1

Syntax error in where clause in postgresql

 fl=s.executeUpdate("
insert into demi(rno,subcode,subname,intm,extm,crd,resultdate)
values(
  '13JG1A05A0',
  'RT22058',
  ' FREE OPEN SOURCE SOFTWARE(FOSS) LAB ',
  '20',
  '70',
  '2',
  'MAY 2015'
)
end where not exists(SELECT * FROM demi WHERE rn0 ='13JG105A0' AND subcode='RT22058')
");

I'm working in jsp with postgresql as backend, my IDE shows error in this statement. i want to insert a record into db after checking and making sure that no such record already exists

Is this statement correct, or am I trying a garbage code?

Please help, thanks in advance

Upvotes: 0

Views: 191

Answers (2)

user330315
user330315

Reputation:

The reason for your error message is that an INSERT statement does not allow a WHERE clause.

You can only add a where clause to a SELECT statement (or a DELETE or UPDATE statement)

So you would need to get rid of the VALUES clause and use the insert into .. select ... syntax:

insert into demi(rno,subcode,subname,intm,extm,crd,resultdate)
select '13JG1A05A0',
       'RT22058',
       ' FREE OPEN SOURCE SOFTWARE(FOSS) LAB ',
       '20',
       '70',
       '2',
       'MAY 2015'
where not exists (SELECT * 
                  FROM demi 
                  WHERE rn0='13JG105A0' 
                    AND subcode='RT22058');

However for the intended use case:

I'm trying to insert a particular record into db if and only if there exists no other record with same rno and subject code columns

there is a better alternative if you have a unique constraint on (rno, subcode) (which you should) - use the on conflict clause:

insert into demi (rno, subcode, subname, intm, extm, crd, resultdate)
values
 (
  '13JG1A05A0',
  'RT22058',
  ' FREE OPEN SOURCE SOFTWARE(FOSS) LAB ',
  '20',
  '70',
  '2',
  'MAY 2015'
)
on conflict (rno, subcode) do nothing;

Again: the on conflict will only work if you have a unique constraint (or index) on those two columns.

Unrelated, but:

you should specify each constant value with a literal matching the underlying data type. '20' is a character constant, 20 would be a number. Ìf intm, extm and crd re numbers, don't provide character values. Also if resultdate is a date column 'MAY 2015' won't work either.

Upvotes: 1

Evan Carroll
Evan Carroll

Reputation: 1

Not sure how

end where not exists(SELECT * FROM demi WHERE rn0 ='13JG105A0' AND subcode='RT22058')

got into your code, but the whole thing is suspicious what do you even want that to do?

Upvotes: 0

Related Questions