Обсуждение: Problem with simple update query
Hi All,
Would appreciate some help with something that I think should be really easy but I've been tearing my hair out with all day.
I want to execute a query which adds a new row into a table or updates an existing row if the id already exists.
Something like the query I would like to execute is shown below... (I am trying to use Npgsql in C# incidently).
BEGIN
IF :ID < 0 THEN
INSERT INTO myTable DEFAULT VALUES;
:ID = curval(properties);
INSERT INTO myTable DEFAULT VALUES;
:ID = curval(properties);
END
UPDATE myTable SET
type = :TYPE,
address_id = :ADDRESS_ID,
address_line1 = :ADDRESS_LINE1,
address_line2 = :ADDRESS_LINE2,
address_line3 = :ADDRESS_LINE3,
town = :TOWN,
county = :COUNTY,
WHERE id = :ID;
COMMIT
With trying variations of the above, the problems seem to be:
1. I can't set the :ID value other than as it is already a parameter
2. With the "IF" statement included the query always fails with "Syntax Error near IF"
Currently, I'm going to carry on working by building different sql statement to insert or update based on wether my application knows that an INSERT or UPDATE is needed, but I doubt this is the correct way of doing it.
One thing I really don't want to do is have to create stored procedures to handle all inserts / updates etc as this seems to me to be alot of extra overhead and won't actually improve the security of my application.
Any help would be very gratefully received.
Thanks.
Simon.