+24Hello all,
Another newby question, sorry :)
I have a table in PostgreSQL which is called ExaminationNumber and has a single column call ExamID.
The ExamID field stores a current count of the examinations processed and is increased by one each time.
I am have a reader that reads from the table and grabs the current ExamID value. I then want to update the value by one using the SQLExecutor but can't get it to work.
Can anyone let me the correct syntax or if there is a better way to go about this?
Best answer by nielsgerrits
What is the feedback in the log?
I think the syntax is the problem. It's either "insert into" or "update", not "update into". And you probably need to use "where" to define which record(s) need to be updated. In your current statement all records will be updated. See for example W3Schools. Also, postgres is case sensitive, so if you have table names or column names with uppercase characters you need to use quotation marks.
update public."ExaminationNumber" set "ExamID" = (@Evaluate(@Value(ExamID)+1));Best practice is first to make the statements work, for example using pgAdmin, then transmit the statement to FME.
As an addition, in this particular example I would use the value which already is in the database to reduce I/O.
For all records this would be
update public."ExaminationNumber" set "ExamID" = "ExamID" + 1;For a specific record this would be
update public."ExaminationNumber" set "ExamID" = "ExamID" + 1 where "ExampID" = @Value(ExamID);
+60What is the feedback in the log?
I think the syntax is the problem. It's either "insert into" or "update", not "update into". And you probably need to use "where" to define which record(s) need to be updated. In your current statement all records will be updated. See for example W3Schools. Also, postgres is case sensitive, so if you have table names or column names with uppercase characters you need to use quotation marks.
update public."ExaminationNumber" set "ExamID" = (@Evaluate(@Value(ExamID)+1));Best practice is first to make the statements work, for example using pgAdmin, then transmit the statement to FME.
As an addition, in this particular example I would use the value which already is in the database to reduce I/O.
For all records this would be
update public."ExaminationNumber" set "ExamID" = "ExamID" + 1;For a specific record this would be
update public."ExaminationNumber" set "ExamID" = "ExamID" + 1 where "ExampID" = @Value(ExamID);What is the feedback in the log?
I think the syntax is the problem. It's either "insert into" or "update", not "update into". And you probably need to use "where" to define which record(s) need to be updated. In your current statement all records will be updated. See for example W3Schools. Also, postgres is case sensitive, so if you have table names or column names with uppercase characters you need to use quotation marks.
update public."ExaminationNumber" set "ExamID" = (@Evaluate(@Value(ExamID)+1));Best practice is first to make the statements work, for example using pgAdmin, then transmit the statement to FME.
As an addition, in this particular example I would use the value which already is in the database to reduce I/O.
For all records this would be
update public."ExaminationNumber" set "ExamID" = "ExamID" + 1;For a specific record this would be
update public."ExaminationNumber" set "ExamID" = "ExamID" + 1 where "ExampID" = @Value(ExamID);Thanks @nielsgerrits, you are a champion again.