November 5, 2012 at 2:46 pm
I have two tables .Both have same columns except in table1 i have flag column which i need to update with 'yes' or 'no' after joining two tables on day and id .I need to update Flag column in the Table 1 based on if the Id -day Combination from Table 1 is found in Table 2 .Both tables have more than 50 million rows.
create table #th( day datetime, id int,flag varchar(10))
create table #th2 ( day2 datetime,id2 int)
insert into #th values ('2012-10-28',1,''),('2012-10-28',2,''),('2012-10-28',1,''),('2012-10-28',3,''),('2012-10-27',1,''),
('2012-11-2',2,''),('2012-11-02',2,'')
insert into #th2 values ('2012-10-28',1),('2012-10-28',2),('2012-10-28',1),('2012-10-28',3),('2012-10-27',1),
('2012-10-23',9),('2012-10-23',10),('2012-10-22',11),('2012-10-20',30)
select * from #th
select * from #th2
November 5, 2012 at 3:38 pm
weston_086 (11/5/2012)
I have two tables .Both have same columns except in table1 i have flag column which i need to update with 'yes' or 'no' after joining two tables on day and id .I need to update Flag column in the Table 1 based on if the Id -day Combination from Table 1 is found in Table 2 .Both tables have more than 50 million rows.
create table #th( day datetime, id int,flag varchar(10))create table #th2 ( day2 datetime,id2 int)
insert into #th values ('2012-10-28',1,''),('2012-10-28',2,''),('2012-10-28',1,''),('2012-10-28',3,''),('2012-10-27',1,''),
('2012-11-2',2,''),('2012-11-02',2,'')
insert into #th2 values ('2012-10-28',1),('2012-10-28',2),('2012-10-28',1),('2012-10-28',3),('2012-10-27',1),
('2012-10-23',9),('2012-10-23',10),('2012-10-22',11),('2012-10-20',30)
select * from #th
select * from #th2
Nice job with sample data and ddl. Your description is extremely vague but I guess that you want 'yes' when there is a match and 'no' when there isn't.
This is not going to be fast with 50 million rows in each table but this works with your sample data.
update #th set flag = 'yes/no'
from #th
join #th2 on #th.id = #th2.id2 and #th.day = #th2.day2
update #th
set flag = 'no/yes'
where flag = ''
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 5, 2012 at 3:44 pm
If 50 million is too many rows for a single transaction, how about something like this?
Limit the @Cycle variable so it doesnt for ever, or you want to time a couple of iterations.
I would experiment with the batch size. Too small takes for ever, too large may fill the log.
DECLARE @RowCount INT = 1,
@Interval INT = 100000,
@Cycle INT = 1
WHILE @RowCount > 0 AND @Cycle < 100
BEGIN
UPDATE TOP(@Interval) a
SETa.Flag = CASE
WHEN EXISTS (SELECT1
FROM#th2
WHEREday2 = a.day
ANDid2 = a.id
)
THEN '1'
ELSE '0'
END
FROM #th AS a
WHERE a.Flag = ''
SET @RowCount = @@ROWCOUNT
SET @Cycle += 1
END
November 5, 2012 at 4:36 pm
I was trying to use this one.Is this right?
UPDATE #th
SET FLAG= CASE WHEN B.day2 is null then 'NO'
else 'YES' end
from #th A
left join ( select distinct day2, id2 from #th2 )B
on A.day1=b.day2
and a.id1=b.id2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply