I am trying to set up a data point that will update my data table.
I have a table that has a flag to mark which rows have been read into Mango or not.
I would like to update the flag to 1 after the data have been read so that the next time a new row is read.
The MySQL data source has this select statement:
SELECT * FROM db.staging_all where logger_id = 1 and flag = 0 order by date ASC LIMIT 1
And this gives me a good row of data.
My update statement:
UPDATE db.staging_all SET flag = 1 WHERE logger_id = 1 AND flag = 0 ORDER BY date ASC LIMIT 1;
This works in the MySQL workbench, but if I combine the 2 statements together on the data source I get an error message:
class com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE db.staging_all SET flag = 1 WHERE logger_id = 1 AND flag = 0 ORDER BY dat' at line 2
So I think that the update statement must have to go into a data point that is set to "Modify Table Only"
But it doesn't seem to work. My update statement is saved, but I am not able to select any parameters?
I have tried this as well:
UPDATE db.staging_all SET flag = ? WHERE logger_id = ? AND flag = 0 ORDER BY date ASC LIMIT 1;
Any advice would be greatly appreciated.