March 20, 2012 at 9:36 am
All,
Over the years, I've had fits trying to update large tables via join to another table. I currently have a 46 million-record table I need to update via joining to a 1 million-record table. I've created a new field in the larger table that will be populated with data from the smaller table. I've seen joined updates like this run for hours and hours without completing, so I've taken to creating a new table with the product of the join. There has to be a better way, and I'm guessing the problem has to do with SQL Server saving enough information to roll back the change. Since I'm only populating a new field that I created, I don't need that level of protection. Is there an option I can turn off to speed this type of query up? Here's the syntax I'm using:
UPDATE t1 SET field1 = t2.field1
FROM Table1 t1 LEFT JOIN Table2 t2 ON
t2.id1 = t1.id1
AND t2.id2 = t1.id2
Thanks in advance for any tips.
Tom
March 20, 2012 at 9:43 am
tom.mcginty (3/20/2012)
All,Over the years, I've had fits trying to update large tables via join to another table. I currently have a 46 million-record table I need to update via joining to a 1 million-record table. I've created a new field in the larger table that will be populated with data from the smaller table. I've seen joined updates like this run for hours and hours without completing, so I've taken to creating a new table with the product of the join. There has to be a better way, and I'm guessing the problem has to do with SQL Server saving enough information to roll back the change. Since I'm only populating a new field that I created, I don't need that level of protection. Is there an option I can turn off to speed this type of query up? Here's the syntax I'm using:
UPDATE t1 SET field1 = t2.field1
FROM Table1 t1 LEFT JOIN Table2 t2 ON
t2.id1 = t1.id1
AND t2.id2 = t1.id2
Thanks in advance for any tips.
Tom
1. Your query updates all records in Table1, regardless if any match found in Table2. The value will be set to NULL if no match found.
If this column already contains NULL, you don't need LEFT JOIN, Use INNER JOIN to limit number of records updated.
2. You can do your update in batches.
March 20, 2012 at 2:32 pm
also only the records who need to be update...removed left join
UPDATE t1 SET field1 = t2.field1
FROM Table1 t1 JOIN Table2 t2 ON
t2.id1 = t1.id1
AND t2.id2 = t1.id2
where t1.field1 <> t2.field1 --this ensures ur not updateing records who dont need to be.
March 20, 2012 at 2:48 pm
captcooldaddy (3/20/2012)
also only the records who need to be update...removed left joinUPDATE t1 SET field1 = t2.field1
FROM Table1 t1 JOIN Table2 t2 ON
t2.id1 = t1.id1
AND t2.id2 = t1.id2
where t1.field1 <> t2.field1 --this ensures ur not updateing records who dont need to be.
This won't work if either field1 is NULL. Here is an option that will work for any combination of NULL values (including both NULL where you don't need to update).
UPDATE t1 SET field1 = t2.field1
FROM Table1 t1 JOIN Table2 t2 ON
t2.id1 = t1.id1
AND t2.id2 = t1.id2
CROSS APPLY (
SELECT t2.Field1
EXCEPT
SELECT t1.Field1
) AS nv( NewValue )
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 20, 2012 at 2:53 pm
or wrap in sinulls
also only the records who need to be update...removed left join
UPDATE t1 SET field1 = t2.field1
FROM Table1 t1 JOIN Table2 t2 ON
t2.id1 = t1.id1
AND t2.id2 = t1.id2
where isnull(t1.field1,'-1') <> isnull(t2.field1,'-1')
March 21, 2012 at 6:45 am
captcooldaddy (3/20/2012)
or wrap in sinullsalso only the records who need to be update...removed left join
UPDATE t1 SET field1 = t2.field1
FROM Table1 t1 JOIN Table2 t2 ON
t2.id1 = t1.id1
AND t2.id2 = t1.id2
where isnull(t1.field1,'-1') <> isnull(t2.field1,'-1')
Actually, no. This will not update when it should if t1.field1 = '-1' and t2.field1 is null or vice versa.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2012 at 1:29 pm
picky picky. Your right. That's my shorthand and i really shouldn't assume his column data will never have a -1 as it's value.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply