Results 1 to 21 of 21

Thread: [RESOLVED] How to update many columns in many rows from another table

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Resolved [RESOLVED] How to update many columns in many rows from another table

    Essentially I want to import a smaller table of similar data with identical columns. But each extant column has an PK ID column that's automatically created and I want to preserve. So I can't delete all the rows and import the new data with Insert, I need to update each existing row in place.
    Say I have a large table A with 30 columns. I have a smaller table B with 20 columns. The 20 columns in B are a subset of the 30 in A. Each row in A and B have a match in the other. I.E. the combination of two columns creates a unique ID and there is a match for each row in B in A. For each of the matches in A I want to take most of the values from B and update the columns in A.
    I looked online and there seem to be multiple ways to do this. Some I don't understand completely and some seem to extreme. One solution I saw was to do a huge join and use the joined table in the update. Another had a subquery for the new values. Another used a Update Set From Where (highest voted answer) that seems to simple to work, but I want to try it seems elegant. But I don't understand his last line to "avoid empty updates". I'll put his code below.
    Is this last one the best way for what I want to do? If so, can you explain the last line to avoid empty updates?
    SQL Code:
    1. UPDATE table2 t2
    2. SET    the_geom = t1.the_geom
    3. FROM   table1 t1
    4. WHERE  t2.address = t1.address
    5. AND    t2.the_geom IS DISTINCT FROM t1.the_geom; -- avoid empty updates

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,187

    Re: How to update many columns in many rows from another table

    That IS DISTINCT FROM is similar to a '<>' operator (is not equal to) but it behaves more specifically for NULL values. The '=' and '<>' operators are undefined when either or both values being compared are NULL. On the other hand, IS DISTINCT FROM treats NULL as a distinct value, so evaluates to TRUE if both values are NULL and to FALSE if only one value is NULL. Basically, that last condition is preventing the UPDATE being performed when both values are the same, even if that value is NULL.

    For the record, I have never seen that used before and had no idea what it actually meant, so I did a web search and read what I found. Took me just a few minutes to understand it and write this answer. As always, I point this out not to suggest that I'm smarter than everyone else but, rather, to point out that anyone can find that information if I can, specifically because I'm not smarter than everyone else.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,883

    Re: How to update many columns in many rows from another table

    "avoid the empty update" in other words avoid updating a field to a value that's already that value.
    Example, let's say that table2.the_geom is "abc" and the matchin value in table1.the_geom is "abc" ... that would be an "empty update" ... it's setting table2.the_geom to "abc" which it already is.
    Honestly, it's never really been something I've ever worried about. Is it an issue? Meh... if this was something happening all the time at high speeds, and deadlocks become an issue, or records become locked, maybe. If it's a one-off update, or even something done on a regular basis, but isn't uber time sensitive, probably not. IT is good practice, probably, since it would help ease load on the logs, but I don't know that I'd put too much stock in it. At least initially... it might be something to keep in mind for later though.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: How to update many columns in many rows from another table

    Thank you both guys. Is that example the kind of method you would use in this case?

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,187

    Re: How to update many columns in many rows from another table

    Quote Originally Posted by cory_jackson View Post
    Is that example the kind of method you would use in this case?
    It certainly seems reasonable to me but it's not actually something that I've ever had to do so I'm not sure what the alternatives would be that you mentioned. Unless there's a lot of data and something else performs better, I can't see any reason not to use this option.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,883

    Re: How to update many columns in many rows from another table

    As I mentioned, it's not something I've ever bothered dealing with before. The only reason to even think about it is performance, which, after dealing with millions of records and thousands of columns I've never seen an issue with empty updates... and I'm sure that with DBMSs today, they're far more efficient at sussing out such things than I am. But *shrug* eh... I'm not a real DBA either... so..... ehhhh....

    The only downside I see to it, is I'm not sure how you could apply a multi-column update in that construct, other than doing one column at a time, which I don't know would be any more efficient.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: How to update many columns in many rows from another table

    TechGnome: Some of these are greater than 100k rows. One is over a million and about a dozen columns. How would you update multiple columns in many rows?

  8. #8

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: How to update many columns in many rows from another table

    I created this test environment and update command and it works perfectly. The null value on the third row was intentional. Now I'll move up to the real deal.
    I'm still uncomfortable as I don't understand how it works. But I'll come back to that later.
    SQL Code:
    1. Create table Alpha (ID int Identity(1,1) Primary Key, C1 VarChar(9), C2 VarChar(9))
    2. Create table Beta (ID int Identity(1,1) Primary Key, C1 VarChar(9), C2 VarChar(9))
    3. Insert into Alpha (C1,C2) Values ('A','B')
    4. Insert into Alpha (C1,C2) Values ('C','D')
    5. Insert into Alpha (C1) Values ('E')
    6. Insert into Beta (C1,C2) Values ('G','H')
    7. Insert into Beta (C1,C2) Values ('I','J')
    8. Insert into Beta (C1,C2) Values ('K','L')
    SQL Code:
    1. Update Alpha
    2. Set Alpha.C1 = Beta.C1, Alpha.C2 = Beta.C2
    3. From Beta
    4. Where Alpha.ID = Beta.ID

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,883

    Re: How to update many columns in many rows from another table

    What do you mean you don't understand how it works? Why not?

    You do understand how this works, right?
    Code:
    Select Alpha.C1, Aplha.C2, Beta.C1, Beta.C2
    from Alpha
    Inner Join Beta on Alpha.ID = Beta.ID
    It's essentially the same thing...

    Update the table Alpha... set the Fields C1 and C2 with the fields C1 and C2 from table Beta, where the ID in Alpha matches the ID in Beta...

    FYI... to save you a bit of typing... you can do this:
    Code:
    UPDATE Alpha
    SET C1 = Beta.C1, 
            C2 = Beta.C2
    FROM Beta
    WHERE Alpha.ID = Beta.ID
    The table Alpha is implied since it is specified in the UPDATE clause, so it's not needed on the left side of the SET.... BUT... the Beta part is (or what ever alias is used).
    Even though I usually prefer to have things specified, I prefer this format in UPDATES as I find it cleaner and succinct. I know where the data is going, I need to know where it's coming from.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: How to update many columns in many rows from another table

    Sorry for being away so long. Holidays, catching up on work, etc.

    TG that is a valuable tip on the aliasing. I didn't know that. I agree. I like things like that which keep things clearer. Sometimes I'll do long-winded code just so it's easy to understand a year later when I revisit it.

    Conceptually I'm missing something. Usually the value on the right is 'static' for lack of a better word. All the affected rows are updated the same. If thought of as a loop, it goes though each row and changes the value the same for all. But in this example it stops and kind of does a lookup for each based on other column values. I know it probably all seems like "well duh" but it's a new concept for me. I have a problem inherently seeing it. I suppose I just need to use it more to get it. So far in the book I have been reading and learning from there hasn't been any discussion of this.
    And other things like what if there's more than one match? How does it update one column where there are multiple rows that match the Where criteria. I don't know what will happen. I need to understand what will happen in cases like this. Is there a term for this mechanism? Something I can look for in the index of my book. I really want to understand this.

    And thank you for the edification so far. You rock. All of you.

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,883

    Re: How to update many columns in many rows from another table

    If there are multiple matches in the where, then it will update the specified column(s) in the set. And you'll get back something along the lines of "Affected 2 row(s)" if you're running the commands in Management Studio.

    In short it will update all matching rows. If you don't want it to update all of the matching rows, then you need to refine the where clause so that only the rows you do want updated are updated.

    It's like email. If you want the email to go to one recipient, you address it to one person, right? If you want it to go to 5 people, you address it to 5 people... samething in SQL. the where clause refines the rows that are returned and/or affected by the action. If too manyrows are returned/affectd, you refine it until the
    right number is retured/affected. If it's too few, you loosen up the criteria until it's right.

    Code:
    Update sampleTable
    Set fldTest = 0
    Blindly sets the fldTest field in the sampleTable table to 0 for ALL row in the table, no filter.

    Code:
    Update sampleTable
    Set fldTest = 1
    Where ID >= 10
    Sets the fldTest field in the sampleTable table to 1 for all rows in the table, where the ID field is equal or greater than 10.

    Code:
    Update sampleTable
    Set fldTest = 2
    Where ID = 1
    Sets the fldTest field to 2 where the ID is 2... only changes one row.


    The where in an update where EXACTLY the same as it does in a SELECT.
    I will often start muy update comands as selects until I have the right rows coming back, then I switch them to updates.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: How to update many columns in many rows from another table

    You wrote "The where in an update where EXACTLY the same as it does in a SELECT". This is perfect. I didn't realize this. This changes my mind and now I see more clearly. Thank you.
    Now what happens when there is more than one match in the Where you are speaking of? In my example if there was Beta.ID had two rows where it was "9" that matched Alpha.ID where there is only one "9", would it just update the first one?I guess what I'm asking is what happens when they're unmatched? I think if there are multiple in Alpha and one in Beta, all in Alpha get that update. Like a loop for each row.

  13. #13
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,883

    Re: How to update many columns in many rows from another table

    Quote Originally Posted by cory_jackson View Post
    You wrote "The where in an update where EXACTLY the same as it does in a SELECT". This is perfect. I didn't realize this. This changes my mind and now I see more clearly. Thank you.
    Now what happens when there is more than one match in the Where you are speaking of? In my example if there was Beta.ID had two rows where it was "9" that matched Alpha.ID where there is only one "9", would it just update the first one?I guess what I'm asking is what happens when they're unmatched? I think if there are multiple in Alpha and one in Beta, all in Alpha get that update. Like a loop for each row.
    Code:
    UPDATE Alpha
    SET C1 = Beta.C1, 
            C2 = Beta.C2
    FROM Beta
    WHERE Alpha.ID = Beta.ID
    Again... if you ran that as a select, how many rows would be returned? Two right? So... how many rows would be updated? Two... There is no unmatched... the fact that were were two in one table and one in the other is immaterial.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: How to update many columns in many rows from another table

    I did a test to answer my own question. It takes the first row.
    sql Code:
    1. CREATE TABLE Alpha (ID INT, C1 VARCHAR(9))
    2. CREATE TABLE Beta (ID INT, C1 VARCHAR(9))
    3. INSERT INTO Alpha (ID,C1) VALUES (1,'A')
    4. INSERT INTO Beta (ID,C1) VALUES (1,'B')
    5. INSERT INTO Beta (ID,C1) VALUES (1,'C')
    So we have two IDs in Bets that match a row ID in Alpha.
    sql Code:
    1. UPDATE Alpha
    2. SET C1 = Beta.C1
    3. FROM Beta
    4. WHERE Alpha.ID = Beta.ID
    I was wondering how two values, B and C could be pushed into one results row. I wasn't sure if it the Alpha.C1 value would be "B", "C" or would generate an error. Now see the answer is that it takes the first of the two which ends up being "B".
    Thanks again TG.

  15. #15
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,883

    Re: [RESOLVED] How to update many columns in many rows from another table

    That's slightly different from the one you asked
    I think if there are multiple in Alpha and one in Beta
    But... be careful with that because depending on indexes, and order by, or the lack of them... you may or many not be able to guarantee what row comes back first...
    This is why the query part is important... it's not only for the update side, but also on the other side... the source.... I've been bitten by this where unordered data updated things unexpectedly. Don't assume that this one test is the answer... because next time it may not. And I say that because I'd have sworn that it would have taken the last value because I'd swear that's what I thought I had seen it do in the past.... so.... shrug... Try changing the order of your Beta rows:


    Code:
    UPDATE Alpha
    SET C1 = Beta.C1
    FROM Beta
    WHERE Alpha.ID = Beta.ID
    vs.
    Code:
    UPDATE Alpha
    SET C1 = Beta.C1
    FROM Beta
    WHERE Alpha.ID = Beta.ID
    Order By Beta.Id
    vs.
    Code:
    UPDATE Alpha
    SET C1 = Beta.C1
    FROM Beta
    WHERE Alpha.ID = Beta.ID
    Order By Beta.Id Desc

    Se what you end u with in each case...

    Then for some fun, add more records, add indexes... it all changes how the rows are returned...
    This is why I always build my updates as selects first... selecting both sides of the equals.... then when I have the data I want, I order it.... look for any annomalies... then convert it to an update.... stick a BEGIN TRANSACTION at the top.... and then run it.... I then select the data from the table ... and verify that it worked... if it's a one-off udate, I'll commit it if it's good... or roll it back if it isn't. If it's not a one-off, or supposed to be part of a larger query or sproc, I'll roll it back even if it's good... and the incorporate it into the larger query/sproc where it belongs.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: [RESOLVED] How to update many columns in many rows from another table

    Thanks for all that. This was exceedingly hypothetical. I was speaking just more to my understanding of how things work "But what if???". So now i know. But I would never do this kind of thing unless there was one match. In the system I'm working on the combination of two ID columns should always be unique. And if someone violated policy and created duplicates, I can't be held responsible for what happens.
    Thanks again TG.

  17. #17
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,883

    Re: [RESOLVED] How to update many columns in many rows from another table

    Quote Originally Posted by cory_jackson View Post
    This was exceedingly hypothetical.
    Until it isn't ... believe it or not... do this long enough, and eventually you learn there is no theoretical... only experienced reality.

    Quote Originally Posted by cory_jackson View Post
    In the system I'm working on the combination of two ID columns should always be unique. And if someone violated policy and created duplicates, ...
    You would be surprised... several lifetimes ago, there was a system I was working on, we'd regularly get calls from the client claiming that teh system was changing data on its own. This "data change" was causing a data integrity issue. We tested the heck out of it from the front end and couldn't find out how it was happening. On a lark, I decided to try manipulating the data directly from the database, and it let me... creating the problem. So we thought that maybe there was a user going in bedhind the scenes and changing stuff. Don't know why or how exactly, but we rolled with it, added trigger that logged changes to the data, what was changed, who changed it, and when it was changed. We didn't tell the client, and rolled it out. Sure enough, like clockwork, the call came in,we checked the logs... and there it was... some one was using SQL Server Management Studio to update the data directly..... and that user? teh very same one that kept calling in to report "the bug!" So we notified their IT dept and had them lock it down, and that solved that. Point being is that this isn't as hypothetical as you might think. Today when you write the query it may only be one row, tomorrow it could be three.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: [RESOLVED] How to update many columns in many rows from another table

    You're absolutely right. And the data I've been working with originates at government offices and I am appalled at how bad the data integrity is. But in this case I would test first if it was possible. Like you suggest.

    Your story reminded me of one of those impossible scenarios. A tech had a server they was rebooting around the same time every night. They tried all kinds of monitoring software and did many tests but the system appeared perfect. So he went on site and sat in front of the rack and waited. Nothing happened. The next night it happened again. So he went back and it didn't. I can't remember how many times he tried this but the fault seemed to know he was there. I can't remember how he finally figured it out but what was happening was the janitor was unplugging a cord in order t plug in his vacuum cleaner or something. When the janitor saw him there he didn't want to make noise and would skip it. Apparently the rack power supply was all tapped out and someone improvised by plugging it into a regular wall outlet. A great example of a black swan.
    Oh, and of course the slip of paper in the movie The Andromeda Strain.

  19. #19
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,526

    Re: [RESOLVED] How to update many columns in many rows from another table

    thanks Guy's

    I really enjoyed reading Post#17 and #18

    well who knows maby the guy from Post#17 was thrown out and is now a janitor
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  20. #20
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,883

    Re: [RESOLVED] How to update many columns in many rows from another table

    People think that the vaccuum story is a myth... my dad used to work in the IT at a hospital, and saw stuf like that allt he time. They even had one doc who, when the cleaners would come around, he would put his keyboard on the floor and have them run it over the keys (he ate at his desk a lot) .... problem was that vaccuums produce a lot, i mean a LOT of static electricity... which would then enter the keyboard, up the cable and zap the computer.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  21. #21

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    996

    Re: [RESOLVED] How to update many columns in many rows from another table

    LOL That's a good one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width