db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@sbcglobal.net>
Subject Re: using a join in an UPDATE command
Date Fri, 04 Mar 2005 17:07:30 GMT
derby2@os10000.net wrote:

> 
> update keys set ky=badies.locked_key where person=badies.person;
> 

The following is perhaps a tad ugly (and performance might be unacceptable), but I think this
does what you want...?

update keys set ky = (select locked_key from badies where keys.person=badies.person) where
0 != (select count(*) from 
badies where keys.person=badies.person);

The where clause that does "select count(*)" is messy, but it's needed because I assume that
you do NOT want to update 
the "keys.ky" value if there is no corresponding entry in the badies table (if you do, it'll
end up null).

Let me show what you I mean:

// Original table:

ij> select * from keys;
PERSON    |KY
---------------------
alice     |9876
bob       |54321
charlie   |121212
douglas   |343434

4 rows selected

// New table doing the "easier" update:

ij> update keys set ky = (select locked_key from badies where keys.person=badies.person);
4 rows inserted/updated/deleted
ij> select * from keys;
PERSON    |KY
---------------------
alice     |NULL			// I assume you don't want NULL here?
bob       |NULL			// I assume you don't want NULL here?
charlie   |ding
douglas   |dong

4 rows selected

// New table doing the "messy" (but correct?) update:

ij> update keys set ky = (select locked_key from badies where keys.person=badies.person)
where 0 != (select count(*) 
from badies where keys.person=badies.person);
2 rows inserted/updated/deleted
ij> select * from keys;
PERSON    |KY
---------------------
alice     |9876
bob       |54321
charlie   |ding
douglas   |dong

4 rows selected

Of course, maybe there's an easier way to do it, but that's just the one I stumbled upon...

*shrug*
Army


Mime
View raw message