Noah J SILVA wrote: > > Thanks Sergio, > > Actually, I had tried something like this, but the provlem > here is that > the inner query isn't corrolated to the outer query, and so > returns every > row where ot.key = st.key. The outer query then throws an > error "More > than one result row not allowed." > > Thank you, > Noah Silva > IS&T - Programmer Analyst > (215) 419 - 7916 >
Now I am confused. Sergio asked you to do something like this: UPDATE spiffy_table st SET st.field1 = (select field1 from other_table where key=st.key) And this IS a correlated subquery, because st.key is used in the subquery although st is not part of the from-clause in the subquery. And if other_table.key is the full primary key, which is unique, then the subquery has no chance to return more than one resultrow. Therefore I am afraid that you did not use exactly that update, Sergio mentioned. Elke SAP Labs Berlin > > > > "Sergio Samayoa" <[EMAIL PROTECTED]> > 07/23/2003 05:04 PM > > > To: "Noah J SILVA" <[EMAIL PROTECTED]>, > <[EMAIL PROTECTED]> > cc: > Subject: RE: Silly SQL question! > > > As you said, such syntax is MSSQL specific. AFAIK only MSSQL > server and > may > be SyBase support that. > > You have tree options: > > 1. Update each field in various UPDATE statements: > > UPDATE spiffy_table st > SET > st.field1 = (select field1 from other_table where key=st.key) > > UPDATE spiffy_table st > SET > st.field2 = (select field2 from other_table where key=st.key) > ... > > 2. Write an SP. > > 3. Write client application to do that. > > -----Mensaje original----- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] nombre de Noah J SILVA > Enviado el: Miercoles, 23 de Julio de 2003 02:37 p.m. > Para: [EMAIL PROTECTED] > Asunto: Silly SQL question! > > > Hi, > > This is a silly question, not that SAPDB specific I think, > but I want to > do something like: > > UPDATE > st > SET > st.field1 = ot.field1, > st.field2 = ot.field2 > FROM > spiffy_table st, > other_table ot > WHERE > st.key=ot.key > > Only SAPDB doesn't like the FROM clause... (I know, I know.. too much > ms-SQL-server...) > > I am sure this can be done with a subquery, but have had a > mental block on > exactly how... Any ideas? > > Thank you, > Noah Silva > IS&T - Programmer Analyst > (215) 419 - 7916 > > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > > > > > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
