Re: UPDATE SET query Oracle 9i

Thanks guys for your quick reply.
I did miss the t_connector c table in the subquery :-( Now I have...
Any further suggestions?
(The select subquery returns exactly what I need...)

Thanks :-)
Henk

SQL> UPDATE t_connector SET StyleEx =
  2 (SELECT 'FKS=' || ps.Name || ':;FKT=' || pt.Name || ':;'   3 FROM t_connector c, t_operation os, t_operation ot, t_operationparams ps, t_operationparams pt   4 WHERE os.OperationID = ps.OperationID   5 AND c.Start_Object_ID = os.Object_ID   6 AND os.Stereotype = 'FK'
  7 AND ot.OperationID = pt.OperationID   8 AND c.End_Object_ID = ot.Object_ID
  9 AND ot.Stereotype = 'PK')
 10 WHERE c.Connector_ID = t_connector.Connector_ID; WHERE c.Connector_ID = t_connector.Connector_ID

        *
ERROR at line 10:
ORA-00904: invalid column name

SQL> SELECT 'FKS=' || ps.Name || ':;FKT=' || pt.Name || ':;'   2 FROM t_connector c, t_operation os, t_operation ot, t_operationparams ps, t_operationparams pt   3 WHERE os.OperationID = ps.OperationID   4 AND c.Start_Object_ID = os.Object_ID   5 AND os.Stereotype = 'FK'
  6 AND ot.OperationID = pt.OperationID   7 AND c.End_Object_ID = ot.Object_ID
  8 AND ot.Stereotype = 'PK'
  9 ;

'FKS='||PS.NAME||':;FKT='||PT.NAME||':;'

FKS=accountId:;FKT=messageId:;
FKS=messageId:;FKT=messageId:;
FKS=senderId:;FKT=messageId:;
FKS=messageAttachmentId:;FKT=messageId:;
FKS=accountId:;FKT=messageId:;
FKS=recipientId:;FKT=messageId:;
FKS=recipientId:;FKT=messageId:;
FKS=contactsId:;FKT=contactId:;
FKS=contactGroupId:;FKT=contactId:;
FKS=accountId:;FKT=contactId:;
FKS=recipientId:;FKT=contactId:;

Pete Finnigan <pete_at_petefinnigan.com> wrote in message news:<bxC6WYBKcGV$EwM2_at_peterfinnigan.demon.co.uk>...
> Hi
>
> Your AND on line 10 should be WHERE!.
>
> hth
>
> kind regards
>
> Pete
Received on Thu Sep 04 2003 - 18:48:32 CDT