UPDATE problems

[yigalm]

Hi,

I am trying to update certain columns within one table with data from another table.
Eventually, this query is for MySQL, but I am first testing it in Access.
The error message the I am getting is: "Operation must be an updateable query".
What am I doing wrong?:

Update pr2_users_values
Set
f_comp_name = (select compname from workstat),
f_node_addr = (select node_addr from workstat),
f_tcp_ip = (select tcpip from workstat),
f_cpu = (select cpu from workstat),
f_cpu_speed = (select cpu_speed from workstat),
f_ram = (select ext_mem from workstat),
f_drive_c = (select drive_c from workstat),
f_c_free = (select free_c from workstat),
f_drive_d = (select drive_d from workstat),
f_d_free = (select free_d from workstat)
Where f_login = (select login from workstat);

Thank you,
IM
igor@acwis.org

 

[tlbroadbent]

You should be using a JOIN rather than numerous sub-queries. The following is the Access syntax. DB2 may be different.

NOTE: I use a and b as aliases for the table names to reduce the amount of text.

Update pr2_users_values As a
Inner Join WorkStat As b
On a.f_login = b.login
Set
a.f_comp_name = b.compname,
a.f_node_addr = b.node_addr,
a.f_tcp_ip = b.tcpip,
a.f_cpu = b.cpu,
a.f_cpu_speed = b.cpu_speed,
a.f_ram = b.ext_mem,
a.f_drive_c = b.drive_c,
a.f_c_free = b.free_c,
a.f_drive_d = b.drive_d,
a.f_d_free = b.free_d Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[yigalm]

I just tried your query, and it produced the same infamous error message: "Operation must be an updateable query"

 

[tlbroadbent]

Does the query return multiple WorkStat records for each f_login in the pr2_users_values table? If so, the record set is not updateable. You'll need to limit the records returned to one for each f_login.

Other questions that may help narrow down the problem:

Are the tables native Access tables or linked tables?
Are both tables updateable?
Do the tables have primary keys?

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[yigalm]

This is right, for the same "f_login" there are a few fields that need to be updated. But I tried to delete the other rows and update only one field (i.e. f_comp_name), and it still gave the same error.

The destination table was exported from MySQL, and the two sourse tables are linked from MySQL. The purpose is to run the query in MySQL, but for now I am just testing it in Access.

The tables are updateable, as far as I know. But the error message states that the 'query' must be updateable?

The tables do not have primary keys, since they were linked or imported. But the real destination table, (which I didn't touch so far)which is now in MySQL does have a primary key.

You see, the sourse tables were originally from FoxPro (.dbf) and then they were dumped into MySQL in order to run a query which will update the MySQL database. This procedre needs to be done on a regular basis. And I can't get it working

 

[tlbroadbent]

When I asked about multiple records, I was referring to rows on the Workstat table not fields to be updated. You can update multiple columns with one update query.

Does Workstat contain duplicate or multiple rows with the same login? If so, the query will not be updateable in access.

Access must be able to uniquely identify each row of linked tables to update them. If the tables have a primary key on MySQL, that should be sufficient.

Have you tried to update the linked tables? You need to know if the tables are updateable. If one of them is not updateable, the query will not be updateable. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[yigalm]

Yes, I am trying to update many rows, where any row represents a unique user. Therefore, the logins are unique.

I am looking at the linked tables, and I cannot add new records to them. So must be that they are not updateable. However, I am only using these as a sourse for the destination table which is updateable.

Could that be the problem with the query? So if I would make these linked tables updateable then the query would work?

 

[tlbroadbent]

Making the linked table updateable may solve the problem. There may be additional issues to resolve after that. Try it and see! ;-) Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[yigalm]

YES!!!
It worked!
You Rule!

Do you know how many people I asked that question today?? I spent on it the whole day!

You got it! I don't know how to thank you!

PS. But why should the sourse tables be updateable?..

 

[tlbroadbent]

I wish I knew why the source table must be updatable in Access. It has been a frustration to me, also. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.