Update table

[kafsar]

I have 2 tables and update data from one table to another. need to update one or two filed only.

inout.Execute "UPDATE Staff_test " _
& "SET name = (select name from staff) where empno = '102' ;"

while excute received the following error:

Subquery returned more the 1 value. This is not permitted when the subquery follow =,!=,<, >= or when the subquery is used as an expression.

if I use like:

inout.Execute "UPDATE Staff_test " _
& "SET name = (select name from staff where staff.empno = '102' ) ;"

Then no error but it update all records not specified one.



Can someone fix pls.

Thanks

[Phil Weber]

You need to include the WHERE clause on both the main query and the subquery:

UPDATE Staff_test
SET name = (SELECT name FROM staff WHERE staff.empno = '102' )
WHERE empno = '102';