If you're reading anything past the title of this article, then I assume you are looking for some seriously geeky stuff. By the way, you're sick and you need some sunshine...

Table A Needs Table B's Data
Often we find ourselves (that is only us nerds) trying to update data in one table that is some sort of machination of another table's data. If we are using big tables, each with more than 10 million rows, and we are updating thousands of records, then we might first try a
correlated sub-query update that looks something like this:

update phone_book a 
    set (name, street, street2, city, state, zip) = 
        (select b.name, b.street, b.street2, b.city, b.state, b.zip 
        from new_phone_book b 
        where a.pbid = b.pbid)
where a.pbid in (select pbid from new_phone_book)

I get that you looked this up already and found that the best way to do this is with a Correlated Sub-query. And I also get, that you spent forever trying to find the best correlated sub-query syntax that would work, because it is awfully difficult to find anyone who knows squat about updating more than 1 column at a time from data from another table. You may have even played with the where clause by taking it off or creating some new indices.

However, now that you created your lovely sub-query and have tried a million ways to run it, you are stuck because you tried to execute it an hour ago and it is now filling up your redo log space.

Quick note: go ahead and kill your update now. It sucks and may never finish... For help on how to find blocking sessions and how to kill them, see my Merc 'em blog

Now for a better way. Can you hear the birds singing and the soft wind blowing through the trees all signaling that the green meadows are just up ahead? I call this next technique, an *Inline View Update. Not very sexy or catchy, but dang, the code works a heck of a lot better!

-- Inline View Update
update (
        select a.pbid, a.name as aname, a.street as astreet, a.street2 as astreet2, 
            a.city as acity, a.state as astate, a.zip as azip, 
                b.name as bname, b.street as bstreet, b.street2 as bstreet2, 
            b.city as bcity, b.state as bstate, b.zip as bzip
    from phone_book a, new_phone_book b 
    where a.pbid = b.pbid
)
set aname = bname, astreet = bstreet, astreet2 = bstreet2, acity = bcity, astate = bstate, azip = bzip

Admittedly, the code looks super ugly compared to a nicely formatted correlated sub-query. And the aliases are a pain to type, but who cares, it friggin works and fast. It will outrun a home-schooled teenage groom on his wedding night and the other query above by exponential factors AND you won't have to fiddle with it. Just do it, so you can get on with whatever you were trying to do before you found my stuff so dang helpful!

As always, comment below or shoot me any questions on this. Also note, this really only applies to ORACLE and I will be posting some SQL Server correlated update syntax later. :)

Also note, even trying the new way of joining tables with from phone_book a inner join new_phone_book b on a.pbid = b.pbid works better with inline views.