Home > IT Answers > Microsoft Windows > update single row error
Help

Question

  Asked: Oct 20 2007   2:43 PM GMT
  Asked by: Mandrews81

update single row error


Update, multiple row query

This query returns a single row error while attempting to update the mfr_name_id in the property table with the values in the eqp_mfr_names table where property.mfrcode = eqp_mfr_names.mfr_code why?

UPDATE property p
SET (mfr_name_id ) = (
SELECT (mfr_name_id)
FROM eqp_mfr_names
WHERE mfr_code = p.mfrcode)

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Oct 22 2007  0:44 AM GMT  by Mandrews81




This worked but is there an update query that would do the same?
begin
for rstid in (select distinct mf.mfr_name_id, mf.mfr_code
from eqp_mfr_names mf,property p
where p.mfrcode = mf.mfr_code) loop
update property
set mfr_name_id = rstid.MFR_NAME_ID
where mfrcode = rstid.mfr_code;
end loop;
end;
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Microsoft Windows and Database.

Looking for relevant Microsoft Windows Whitepapers? Visit the SearchEnterpriseDesktop.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Tudz Drkl  |   Oct 25 2007  1:09PM GMT

Your update query will work - this is a very common update method - only if the correlated subquery returns only one row for each mfr_code. Check your data by looking for multiple rows by GROUPING BY mfr_code. Also make sure you do not have any NULL mfr_codes. Another good practice is to always add an exists clause to a correlated update. This exist clause will include the same join criteria and predicates as the update subquery and ensure you do not update rows not intended for update:

AND EXISTS ( SELECT 1
FROM eqp_mfr_names
WHERE mfr_code = p.mfrcode)