update query
-
I need to update a table with userids.
The users ids come from another table.
I have tried the following code,
update tbl_sample_conf_samp_test
set uid =
(select s.password
from pwtable_test s, tbl_sample_conf_samp_test t
where s.orcsampleid = t.orcsampleid)
but I receive the following error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
can anyone help asap?
Thanks
-
Can you show simply examples of the two tables (columns etc) and describe how they are realted, and what column(s) you want to update with values from which column(s)..?
(your example implies that you're trying to set the column uid to the value of password.. is this correct?)
/Kenneth
-
Melanie,
You have duplicate IDs on your tables. You either get rid of the duplicates or find a way to uniquely Identify the passwords!
Just check orcsampleid on BOTH tables
hth
* Noel -
nope - I dont have duplicate id's in any other tables
-
Yes, you are right - the uid column at present is blank - what I need to do is to update the uid column with the passwords listed in the password table (pwtable_test) where the orcsampleid (unique identifier) from the password table matches the orcsampleid from the tbl_sample_conf_test table
the password table has 2 columns, 1 called orcsampleid (all unique) and the other called password.
the tbl_sample_conf_test table has a number of different columns however, the orcsampleid column in this table matched orcsampleids in the password table
hope this helps to find me a solution!
-
This is the right syntax:
update t set uid = s.password
from tbl_sample_conf_samp_test t join pwtable_test s
on s.orcsampleid = t.orcsampleid
* Noel -
thanks a million noel - that worked fine
-
Lastly, just an explanation of the subquery errormessage...
As it was written, the subquery would return all passwords for all rows where it found matching id's between the two tables - and that would surely be just as the errormessage stated: more than one row returned
Debugging tip: when encountering these subquery messages, just run the subquery by itself and see what it returns. This often puts one in the right direction to fix it pretty fast.
/Kenneth
-
thanks for the tip Kenneth
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply