[EMAIL PROTECTED] 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
