update single row error

pts.
Tags:
Update
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)

Answer Wiki

Thanks. We'll let you know when a new response is added.

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;

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Tudz Drkl
    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)
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following