SQL UPDATE 1

[UAMI]

Hi!

I need to make an update to a field and it's value must be equal to another field in another table. Thr sql begin's to execute but then it gives an error:

"Result of SELECT more than one row."

I think it's normal, that he return's more than one row but how can i get it done?

The sql statement:
UPDATE bibl/FGDVLD001 SET ESCRITORIO = (SELECT A0AREA
FROM bibl/APOL00 WHERE MOD=A0$RAM AND NAPO=A0NAPO AND MULTI='N'
) WHERE EXISTS (SELECT * FROM ACOGISD#E/APOL00 WHERE
MOD=A0$RAM AND NAPO=A0NAPO AND MULTI='N')


In the detail's of the message, it says:
For a subquery the IN, EXISTS, ANY or ALL predicates can be used to process more than one result row.



But, isn't what i'm doing?

 

[JarlH]

Probably the error message is related to the " = (SELECT A0AREA FROM" part of your UPDATE statement.

Try to run the SELECT statement alone, without doing an update to see how many rows it returns.

 

[PHV]

Do a SELECT DISTINCT in your subquery

Hope This Help
PH.

 

[UAMI]

Thank you, for your help! I got the idea and resolved in this way:

UPDATE bibl/FGDVLD001 SET ESCRITORIO = (SELECT DISTINCT A0AREA
FROM ACOGISD#E/APOL00 WHERE MOD=A0$RAM AND NAPO=A0NAPO and multi='N'
) WHERE exists
(SELECT distinct a0area FROM ACOGISD#E/APOL00 WHERE
MOD=A0$RAM AND NAPO=A0NAPO and multi='N')

This takes much time do execute, for 7500 registers, but work's!

 

[PHV]

The DISTINCT is not mandatory in the WHERE sub-select.