Working through some insert/update queries on an application today and came across a result I hadn't expected.
Queries
My insert queries look similar to this:
PARAMETERS nm TEXT(10), st TEXT(2);
INSERT INTO park(pname, pstate)
VALUES([nm],[st]);
And their companion updates were like this:
PARAMETERS id LONG, nm TEXT(10), st TEXT(2);
UPDATE park
SET
pname = [nm], pstate = [st]
WHERE
ID = [id];
The table they were updating was similar to this:
park
ID LONG | pname TEXT(10) | pstate TEXT(2)
Unexpected Result
Working through writing the queries, I tested each by running it against the database and providing test values for the various parameters. After the insert query, I'd test the update by updating the newly inserted record.
In most cases the tables were empty, so the update would simply update the single record in place.
However as soon as I ran the update on a previously populated table, I found the query was attempting to update ALL records, not just the one whose ID was being provided through the parameter.
The question is why??
Problem
While ID certainly was a field in the park table, using id as a parameter was essentially stating the following in the WHERE clause:
WHERE ID = id;
or
WHERE ID = ID;
Since ID is always equal to itself, the UPDATE attempted to update ALL records instead of only the expected record with the provided ID.
Solution
To fix the problem, I simply used the first and last letter of the table being updated before the id for each case I was updating a record identified by its ID. So the working code - that updates only the record identified - is:
PARAMETERS pkid LONG, nm TEXT(10), st TEXT(2);
UPDATE park
SET
pname = [nm], pstate = [st]
WHERE
ID = [pkid];
Obviously I wasn't paying close attention while I was writing the query -- while I'd used different names for other parameters, I didn't do so when it came to the ID for the update query.
Bottom Line
Whenever working with paramaterized queries, make sure your parameter names DO NOT match your table's field names.
Doing so will avoid the issue noted above as well as other related issues.
You'll also want to avoid reserved words for your table, field, and parameter names.
Hope this helps someone avoid a possibly nasty surprise when updating records -- or trying to figure out why their parameter queries didn't seem to work.