Trying to "update from" - Derby 10.1

[Jim Murphy <ji...@pobox.com>]

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-tp14778557p14778557.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Trying to "update from" - Derby 10.1

[Jim Murphy <ji...@pobox.com>]

I support 5 different DB types and each have a single script file that
performs each version's migration.  Our product is pretty widely available
and deployed - for dev tools.  For architectural simplicity I was hoping to
stay with this pattern.  I run the script s part of my application -  if the
user chooses to do an inplace upgrade. But for militant, enterprise DBAs the
app can just provide the scripts that they can inspect and run on their own. 
If there is custom code it dirties up this process.

As it turns out I'm written this "manual" blob creator but its the first
time I've had to do it - and I don't like doing it.

Thanks for the help.

Jim


Bryan Pendleton wrote:
> 
>> My last problem is converting varchars to blob records.  Anyone have any
>> insights?
> 
> I'm afraid I don't have a lot of experience working with blobs, but is it
> workable for you to write a Java program to perform this task? If so, it
> seems like you should be able to:
>   - select the varchar data into a Java string using JDBC.
>   - create a CLOB object in Java using the string data
>   - set the CLOB object into your database using JDBC.
> 
> I think you can find lots of blob/clob example programs in the Derby
> source tree, in the "java/testing" subdirectory.
> 
> thanks,
> 
> bryan
> 
> 
> 

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

Re: Trying to "update from" - Derby 10.1

[Bryan Pendleton <bp...@amberpoint.com>]

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

I'm afraid I don't have a lot of experience working with blobs, but is it
workable for you to write a Java program to perform this task? If so, it
seems like you should be able to:
  - select the varchar data into a Java string using JDBC.
  - create a CLOB object in Java using the string data
  - set the CLOB object into your database using JDBC.

I think you can find lots of blob/clob example programs in the Derby
source tree, in the "java/testing" subdirectory.

thanks,

bryan

Re: Trying to "update from" - Derby 10.1

[Jim Murphy <ji...@pobox.com>]

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.