UPDATE problems 1

[yigalm]

Hi,

I seem to have similar difficulties discussed here, and I still didn't get it working.
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

 

[Cagliostro]

is it an access query? Now MySQL doesn't support all kninds of subqueries, as in SQL Server for example. Ion Filipski

filipski@excite.com

 

[yigalm]

I am trying to run this in Access for testing pusposes. And this is when I get the error message.
However, the goal is to run this kind of a query in MySQL. Is there another syntax you would suggest to try in MySQL?

 

[Cagliostro]

update one single column in one single update statement. Ion Filipski

filipski@excite.com

 

[yigalm]

I looked at documentation on UPDATE, and it seems to me that it could be used with multiple columns.

Still, I did try updating just one field. I still got the error message.

 

[yigalm]

Finally, the query is working in Access.
Now, the goal is to get it working in MySQL.
I am trying to run it in MySQL and it gives errors.
How is MySQL syntax different from Access SQL?

Here are two versions of the same query which work in Access, but neither one works in MySQL. Can anyone help me 'translate' either version to MySQL?

Thanx,
IM

UPDATE pr2_users_values, workstat
SET
pr2_users_values.f_comp_name = workstat.compname,
pr2_users_values.f_node_addr = workstat.node_addr,
pr2_users_values.f_tcp_ip = workstat.tcpip,
pr2_users_values.f_cpu = workstat.cpu,
pr2_users_values.f_cpu_speed = workstat.cpu_speed,
pr2_users_values.f_ram = workstat.ext_mem,
pr2_users_values.f_drive_c = workstat.drive_c,
pr2_users_values.f_c_free = workstat.free_c,
pr2_users_values.f_drive_d = workstat.drive_d,
pr2_users_values.f_d_free = workstat.free_d
WHERE
f_login=workstat.login;

------------

UPDATE pr2_users_values
INNER JOIN workstat
ON pr2_users_values.f_login=workstat.login
SET
pr2_users_values.f_comp_name = workstat.compname,
pr2_users_values.f_node_addr = workstat.node_addr,
pr2_users_values.f_tcp_ip = workstat.tcpip,
pr2_users_values.f_cpu = workstat.cpu,
pr2_users_values.f_cpu_speed = workstat.cpu_speed,
pr2_users_values.f_ram = workstat.ext_mem,
pr2_users_values.f_drive_c = workstat.drive_c,
pr2_users_values.f_c_free = workstat.free_c,
pr2_users_values.f_drive_d = workstat.drive_d,
pr2_users_values.f_d_free = workstat.free_d;

 

[Cagliostro]

try update ..... from ..set of tables...., maybe it'll work in MySQL. Ion Filipski

filipski@excite.com