[Openroad-users] Conversion to OpenSQL

[Pete Rabjohns]

If you also want consider Oracle, then the syntax is not so easily
mapped.

UPDATE target
SET (col1,col2,col3...) = (SELECT srccol1, srcol2, srccol3... from
srctable WHERE <join_criteria>)
WHERE <target_criteria>

But you will need to consider missing joins, as this is effectively an
outer join query:

So either:

a) add a WHERE EXISTS on the target_Critera to the source table
(remembering to add appropriate indexes for performance) - not sure if
the optimiser always makes the most efficient query for this - I think
you end up doing the sub-query twice - so best avoided if you can.

b) Add NVL clauses to detect and cope with NULL rows

c) Re-write the query as an updateable dynamic view, there are
restrictions on using these but you can do the following in Oracle:

update (select col1 from tab1, tab2 where tab1.pk = tab2.fk and tab2.pk2
= something) t1
set t1.col1 = value;

Anyway, 

As you can see things vary quite a lot if you want to support multiple
databases - so your best bet is to break down your update query into
it's constituent parts

<target table>
<target columns> (as an array)
<source columns/expressions> (as an array)
<source tables>
<source joins/where>
<target joins/where>

This will then allow you to dynamically and generically build an SQL
statement that is appropriate for the underlying database and hide the
implementation from the programmer.


As a side note, updateable dynamic views are also supported my MS SQL,
but I believe that they also have different limitations. 


-----Original Message-----
From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Paul White
Sent: 23 January 2008 01:28
To: International OpenROAD Users
Subject: Re: [Openroad-users] Conversion to OpenSQL

When we enhanced our apps to support ea/mssql, we had to modify the
syntax for UPDATE FROM using execute immediate.[Pete Rabjohns] c

If ingresdb then
 UPDATE mytable 
 FROM othertable
 SET myfield = otherfield
 WHERE mykey = otherkey;

else
 lv_sql =	' UPDATE mytable ' +
		' SET myfield = otherfield ' +
		' FROM othertable ' +
		' WHERE mykey = otherkey'
 EXECUTE IMMEDIATE lv_sql;

Chris Clark wrote
    * UPDATE <table> FROM -- Not OpenSQL, you _may_ be able to do some
      host specific via *DIRECT* EXECUTE IMMEDIATE

________________________________________________________________
OpenROAD-Users mailing list

You can maintain your subscription here:
http://www.peerlessit.com/mailman/listinfo/openroad-users

To unsubscribe click on this link
mailto:openroad-users-unsubscribe at peerlessit.com&subject=unsubscribe

To subscribe click on this link
mailto:openroad-users-subscribe at peerlessit.com&subject=subscribe