db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jim Murphy <jim.mur...@pobox.com>
Subject Re: Trying to "update from" - Derby 10.1
Date Sun, 13 Jan 2008 03:23:05 GMT

Found my own answer by remembering (from the deep penetrailia of my mental
archives) something about correlated subqueries.

That was the ticket.

Instead of writing what I expected (from SQL Server and MySQL):

UPDATE a set a.foo = b.bar FROM A a, B b where a.x = b.y;

For Derby I write:
UPDATE A set foo = (select b.bar B b where A.x = b.y);

Obviously you can do complex subquieries across tables, the select (b.bar B
b where A.x = b.y) that is.  The "correlated" part is the includion of table
A in the subquery.

Another example:

First, as a correlated subquery:

Update hotbooks set title = (select title from books b, targets t where
b.hitcount > t.hot and t.genre = hotbooks.genre)  where
hotbooks.inventorylevel > 0


Second, using the familiar join syntax I knew beforehand:

Update hb set title = b.title from hotbooks hb, books b, targets t where
b.hitcount > t.hot and t.genre = hb.genre where hb.inventorylevel > 0



My last problem is converting varchars to blob records.  Anyone have any
insights?

Cheers,

Jim




Jim Murphy wrote:
> 
> I need to update several rows with data from another table.  I don't know
> the data values at the time I write the statement.  I just want to copy
> from one table into a different table according to some criteria.
> 
> In another dialect I'd write:
> 
> UPDATE a set a.foo = b.bar FROM A a, B b where a.x = b.y;
> 
> How do I do the equiv in Derb 10.1?
> 
> Cheers,
> 
> Jim
> 
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Trying-to-%22update-from%22---Derby-10.1-tp14778557p14781815.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message