-
Dec 30th, 2019, 09:19 PM
#1
Thread Starter
Frenzied Member
[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:
UPDATE table2 t2
SET the_geom = t1.the_geom
FROM table1 t1
WHERE t2.address = t1.address
AND t2.the_geom IS DISTINCT FROM t1.the_geom; -- avoid empty updates
-
Dec 30th, 2019, 09:49 PM
#2
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.
-
Dec 30th, 2019, 09:56 PM
#3
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
-
Dec 30th, 2019, 10:16 PM
#4
Thread Starter
Frenzied Member
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?
-
Dec 31st, 2019, 02:47 AM
#5
Re: How to update many columns in many rows from another table
Originally Posted by cory_jackson
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.
-
Dec 31st, 2019, 07:34 AM
#6
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
-
Dec 31st, 2019, 11:51 AM
#7
Thread Starter
Frenzied Member
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?
-
Dec 31st, 2019, 03:00 PM
#8
Thread Starter
Frenzied Member
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:
Create table Alpha (ID int Identity(1,1) Primary Key, C1 VarChar(9), C2 VarChar(9))
Create table Beta (ID int Identity(1,1) Primary Key, C1 VarChar(9), C2 VarChar(9))
Insert into Alpha (C1,C2) Values ('A','B')
Insert into Alpha (C1,C2) Values ('C','D')
Insert into Alpha (C1) Values ('E')
Insert into Beta (C1,C2) Values ('G','H')
Insert into Beta (C1,C2) Values ('I','J')
Insert into Beta (C1,C2) Values ('K','L')
SQL Code:
Update Alpha
Set Alpha.C1 = Beta.C1, Alpha.C2 = Beta.C2
From Beta
Where Alpha.ID = Beta.ID
-
Dec 31st, 2019, 10:55 PM
#9
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
-
Jan 11th, 2020, 01:26 PM
#10
Thread Starter
Frenzied Member
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.
-
Jan 13th, 2020, 08:22 AM
#11
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
-
Jan 13th, 2020, 01:17 PM
#12
Thread Starter
Frenzied Member
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.
-
Jan 13th, 2020, 02:55 PM
#13
Re: How to update many columns in many rows from another table
Originally Posted by cory_jackson
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
-
Jan 13th, 2020, 05:01 PM
#14
Thread Starter
Frenzied Member
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:
CREATE TABLE Alpha (ID INT, C1 VARCHAR(9))
CREATE TABLE Beta (ID INT, C1 VARCHAR(9))
INSERT INTO Alpha (ID,C1) VALUES (1,'A')
INSERT INTO Beta (ID,C1) VALUES (1,'B')
INSERT INTO Beta (ID,C1) VALUES (1,'C')
So we have two IDs in Bets that match a row ID in Alpha.
sql Code:
UPDATE Alpha
SET C1 = Beta.C1
FROM Beta
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.
-
Jan 14th, 2020, 07:58 AM
#15
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
-
Jan 15th, 2020, 08:40 PM
#16
Thread Starter
Frenzied Member
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.
-
Jan 16th, 2020, 08:02 AM
#17
Re: [RESOLVED] How to update many columns in many rows from another table
Originally Posted by cory_jackson
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.
Originally Posted by cory_jackson
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
-
Jan 16th, 2020, 10:22 AM
#18
Thread Starter
Frenzied Member
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.
-
Jan 16th, 2020, 10:34 AM
#19
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.
-
Jan 16th, 2020, 10:35 AM
#20
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
-
Jan 16th, 2020, 10:45 AM
#21
Thread Starter
Frenzied Member
Re: [RESOLVED] How to update many columns in many rows from another table
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|