Solved

SQL Executor

deanhowell
Badge +9

Hello 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?

 

 

icon

Best answer by nielsgerrits 12 June 2019, 07:45

View original

2 replies

nielsgerrits
Rank
Userlevel 6
Badge +32

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);
deanhowell
Badge +9

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.

 

Reply


Community Stats

28,650
Posts
109,263
Replies
36,523
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.