Re: How to convert Sybase update to suite Oracle ???

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 2 Jul 2005 02:53:39 -0700
Message-ID: <1120298019.817541.192160@g47g2000cwa.googlegroups.com>

DA Morgan wrote:
> vipra wrote:
> > Hi, what is the equivalent oracle DML for the below mentioned sybase one,
> >
> > update Table1
> > set a.column3 = b.value3,
> > a.column4 = b.value4,
> > a.column5 = b.value5,
> > a.column6 = b.value6
> > from Table1 a,
> > Table2 b
> > where a.column1 = b.column1
> > and a.column2 = 1
> > and b.column2 > '28-APR-05'
> > and b.column2 < '01-MAY-05'
> >
> > Works fine in Sybase but errors out in Oracle.
>
> UPDATE Table1 a
> SET (column3, column4, column5, column6) = (
> SELECT b.value3, b.value4, b.value5, b.value6
> FROM Table 2 b
> WHERE a.column1 = b.column1
> AND a.column2 = 1
> AND b.column2 BETWEEN TO_DATE('28-APR-05', 'DD-MON-YY')
> AND TO_DATE('01-MAY-05'));
>
> You can safely assume that Sybase statements, unmodified, either
> will not work in Oracle or will not be optimal in Oracle.
>
> Bookmark the following:
> http://tahiti.oracle.com
> http://www.psoug.org (click on Morgan's Library)
> google.com is your friend too.
>
> And as previously pointed out ... post version, post DDL, post DML,
> and always post complete Oracle error message.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

As an alternative, in Oracle you can update key-preserved views:

UPDATE

( SELECT a.column3
       , b.value3
       , a.column4
       , b.value4
       , a.column5
       , b.value5
       , a.column6
       , b.value6
  FROM   table1 a
       , table2 b
  WHERE  a.column1 = b.column1
  AND    a.column2 = 1

  AND b.column2 > DATE '2005-04-28'
  AND b.column2 < DATE '2005-05-01' )

SET    column3 = value3
     , column4 = value4
     , column5 = value5
     , column6 = value6;

There will need to be a unique constraint on table2.column1. Received on Sat Jul 02 2005 - 04:53:39 CDT