Re: UPDATE SET query Oracle 9i

"Henk Dekker" <hdekker_at_sparxsystems.com.au> wrote in message news:1900676b.0309041827.450fe564_at_posting.google.com...
> hdekker_at_sparxsystems.com.au (Henk Dekker) wrote in message
news:<1900676b.0309012236.2970d95d_at_posting.google.com>...
> > Help required re: Oracle 9i update query...
> >
> > SQL> UPDATE t_connector SET StyleEx =
> > 2 (SELECT 'FKS=' || ps.Name || ':;FKT=' || pt.Name || ':;'
> > 3 FROM 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 AND t_connector.Connector_ID = c.Connector_ID;
> > AND t_connector.Connector_ID = c.Connector_ID
> > *
> > ERROR at line 10:
> > ORA-00933: SQL command not properly ended
> >
> >
> > Thanks
> > Henk
>
> Solved my problem. The original subquery was returning more than 1
> row. The query that works is...
>
> UPDATE t_connector SET (StyleEx) =
> (SELECT 'FKS=' || ps.Name || ':;FKT=' || pt.Name || ':;'
> FROM t_connector c, t_operation os, t_operation ot, t_operationparams
> ps, t_operationparams pt
> WHERE os.OperationID = ps.OperationID
> AND c.Start_Object_ID = os.Object_ID
> AND os.Name = c.SourceRole
> AND os.Stereotype = 'FK'
> AND ot.OperationID = pt.OperationID
> AND c.End_Object_ID = ot.Object_ID
> AND ot.Name = c.DestRole
> AND ot.Stereotype = 'PK'
> AND t_connector.Connector_ID = c.Connector_ID);
>
> (note added conditions SourceRole and DestRole)
>
> Thaks again :-)

Note you don't have an outer where clause so if for a specific record the subquery returns no rows you will update the field to NULL, you may or may not wish this to happen

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK