I've only just started using update queries and have a problem with the
following query in that it comes back with this error: "Subquery returned
more than 1 value. This is not permitted when the subquery follows =, !=, <,
<= , >, >= or when the subquery is used as an expression."

Query:

UPDATE tbl_customerpassword
SET tbl_customerpassword.passnumber = '1111'
where tbl_customerpassword.customerid = (
select cp.customerid
from tbl_customerpassword as cp
inner join tbl_customerdetails as cd
on cp.customerid = cd.customerid
WHERE cd.subscribed = 1
and cp.registered = 1)

Tables:

tbl_customerpassword
customerid passnumber registered
------------ ---------------- ------------
1 1234 1
2 1234 1
3 1234 1
4 1234 1
5 1234 0

tbl_customerdetails
customerid subscribed
------------ ---------------
1 1
2 1
3 1
4 1
5 0

So the error is basically telling me that because the select query is
producing multiple results (4) it cannot do the update? How do I perform an
update an a field in one table while quering the values of another table
(hope that makes sense)?

Thanks,
--
Chris Michael

The problem is that your subquery isn't correlated with the table in the
UPDATE. You've referenced tbl_customerpassword again within the subquery
rather than referencing the outer table.

It looks like this may be the UPDATE you were attempting:

UPDATE tbl_customerpassword
SET passnumber = '1111'
WHERE registered = 1
AND EXISTS
(SELECT *
FROM tbl_customerdetails
WHERE customerid = tbl_customerpassword.customerid
AND subscribed = 1)

Test this out or make a backup before using it on your live data.

--
David Portas
SQL Server MVP
--