Update table

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

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';