Re: Conversion of Update from MSSQL generates "SQL command not properly ended"

Hi,

"Puck" <calchris2002_at_yahoo.com> wrote in message news:d62bc31c.0407231316.60c08386_at_posting.google.com...
> Oracle 9i (9.0.2)
>
> Using an update like the following:
>
> update table1 set table1.col1 = table2.col1, table1.col2 = table2.col2
> from table1,table2 where table1.recid = table2.recid and table2.recid
> in (1234,4567)
>
> Generates the "SQL Command not properly ended" error.

You are getting an error message because the MSSQL update...from is not a standard SQL statement.

In Oracle, you can either use a correlated subquery:

---
update table1 set (col1, col2) = (select col1,col2 from tdable2 where
recid=table1.recid)
where exists (select 1 from t2 where recid=table1.recid and recid in(1, 2))
---

The subquery,  naturally,  has to return a single row,  otherwise you'll get
an error.

... or you can update a table1/table2 join:

--
update
(select t1.col1 a1, t2.col1 a2, t1.col2 b1, t2.col2 b2
from table1 t1, table2 t2  where t1.recid=t2.recid and t1.recid in (1,2)
)
set a1=a2, b1=b2
--

The latter is more efficient but requires a unique constraint on
table2(recid).

VC