1

I'm using TMyQuery component (from MyDAC suite) with the this sentence:

SELECT id, name, salary, ‘n’ as modified FROM employees

If I change the fields SALARY and MODIFIED I would receive the error “Unknown column MODIFIED in field list”

OK. One solution would be the use of SQLUpdate property, specifying the sentence for update:

UPDATE employees SET name = x, salary = y WHERE id = z

But… this implies that the field NAME would be updated even if the only field really changed is SALARY. And this could lead to problem. Imagine that, at the same time, ComputerA changes the field NAME and ComputerB changes the field SALARY. The data from the last computer to apply would prevail.

Could anyone please help me?

Elivaldo
  • 93
  • 4
  • 2
    Makes sense why you can't update `MODIFIED` since it is not a real field in the DB. But, in any case, if you don't actually have a new value to assign to the `NAME` field then simply don't `UPDATE` the `NAME`, eg: `UPDATE employees SET salary = y WHERE id = z`. But the real question is, why would you have 2 computers updating the same DB record at the same time? Yes, the last `UPDATE` to run will "win", so don't update a field if it doesn't actually need to be changed. And you should synchronize the two computers to avoid overlapping `UPDATE`s. – Remy Lebeau Jul 06 '22 at 21:18
  • Thanks, Remy. I simplified this example to make it easier to understand. My real scenario is like this: the table has 36 fields and chances are that two users change different fields at the same time (not exactly at the same time, mas as you said, the last will win). The object TMyQuery can identify which fields were modified and create dynamically a sentence to update only them. This is not a problem when you have only real fields, but if you have a virtual field then TMyQuery tries to update it too. So, I would like to inform TMyQuery not to update that virtual field. – Elivaldo Jul 06 '22 at 21:53
  • 1
    I don't use MySql or MyDAC, but I would not expect `TMyQuery` to let you modify such fields in the first place. If it is, I would consider that a bug, such fields should be read-only. On the other had, there is nothing stopping you from querying the table structure to see if a given field actually exists or not before allowing it to be modified. – Remy Lebeau Jul 06 '22 at 22:43
  • 1
    Not all fields are independent so simultaneous updates like that can leave you with invalid data. Addresses would be a common example as they are often broken into fields and entered in an inconsistent way for PO BOX / Apartment / street direction etc. Easy to have multiple updates from different users to single field parts leave the address as a whole mangled in various ways. Best to use locking / full updates and/or business processes to make sure what users see is what gets posted and not some amalgamation from multiple users. – Brian Jul 07 '22 at 02:39
  • Thank you, Remy and Brian. I'll consider your suggestions. :) – Elivaldo Jul 07 '22 at 11:27

0 Answers0