asked on

Update a column in one table with the count of rows in another

Hello,

I'm working in SQL Server 2012 and trying to update a field in one table with a count of rows in another table that meet my criteria. It will be easier to demonstrate with some faux data so here you go:

CREATE TABLE #TABLE1 (
	TABLE1ID INT,
	USERID INT,
	TABLE1DATE DATE,
	THECOUNT INT
)
GO
CREATE TABLE #TABLE2 (
	USERID INT,
	TABLE2DATE DATE,
	TABLE2CRITERIA INT
)
GO
INSERT INTO #TABLE1 VALUES
	(118380, 116582, '2014-03-14', 0),
	(118380, 126442, '2014-09-12', 0),
	(250546, 377959, '2009-07-26', 0),
	(787156, 69879, '2010-11-27', 0),
	(1430548, 522015, '2015-02-04', 0),
	(877827, 674129, '2013-10-10', 0),
	(67960, 519332, '2014-10-10', 0),
	(67960, 314561, '2011-11-03', 0),
	(1735514, 414294, '2015-02-18', 0),
	(1353983, 565342, '2009-11-14', 0),
	(1810296, 632287, '2009-11-22', 0),
	(298348, 282491, '2012-05-26', 0)
GO
INSERT INTO #TABLE2 VALUES
	(118380, '2012-01-14', 1),
	(118380, '2010-01-11', 1),
	(118380, '2015-01-14', 1),
	(238280, '2000-02-01', 1),
	(1810296, '1999-01-13', 3),
	(298348, '2012-05-23', 2),
	(298348, '2011-03-10', 9),
	(67960, '2000-01-01', 2),
	(67960, '2000-04-10', 1)

Open in new window

What I'm trying to do is update #TABLE1.THECOUNT with the count of rows in #TABLE2 where #TABLE1.USERID=#TABLE2.USERID AND #TABLE2.TABLE2DATE<#TABLE1.TABLE1DATE AND #TABLE2.TABLE2CRITERIA IN (1,2,3).

I thought I would start with a CTE like so:

;WITH CTE_STUFFTOCOUNT AS
(SELECT
	t2.USERID, t2.TABLE2CRITERIA, t2.TABLE2DATE 
	FROM #TABLE2 AS t2
	WHERE t2.TABLE2CRITERIA IN (1,2,3)
)

Open in new window

And then update #TABLE1.THECOUNT with the count of rows where TABLE2DATE<TABLE1DATE but I'm getting hung up on how to handle the multiple instances of USERID in #TABLE1.

Any help will be REALLY REALLY REALLY REALLY appreciated (my brain is currently tied up in knots). :D

Ryan Chong

are you trying to match #TABLE1.TABLE1ID with #TABLE2.USERID instead?

based on your test data:

ttist25

ASKER

Ahhhh shoot! :)

Thanks Ryan. I screwed up the column names but yes, the join would be on #TABLE1.TABLE1ID=#TABLE2.USERID.

Sorry about that.

Ryan Chong

ok, try verify this (not fully tested) and see if it's working?

IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

CREATE TABLE #TABLE1 (
	TABLE1ID INT,
	USERID INT,
	TABLE1DATE DATE,
	THECOUNT INT
)
GO
CREATE TABLE #TABLE2 (
	USERID INT,
	TABLE2DATE DATE,
	TABLE2CRITERIA INT
)
GO
INSERT INTO #TABLE1 VALUES
	(118380, 116582, '2014-03-14', 0),
	(118380, 126442, '2014-09-12', 0),
	(250546, 377959, '2009-07-26', 0),
	(787156, 69879, '2010-11-27', 0),
	(1430548, 522015, '2015-02-04', 0),
	(877827, 674129, '2013-10-10', 0),
	(67960, 519332, '2014-10-10', 0),
	(67960, 314561, '2011-11-03', 0),
	(1735514, 414294, '2015-02-18', 0),
	(1353983, 565342, '2009-11-14', 0),
	(1810296, 632287, '2009-11-22', 0),
	(298348, 282491, '2012-05-26', 0)
GO
INSERT INTO #TABLE2 VALUES
	(118380, '2012-01-14', 1),
	(118380, '2010-01-11', 1),
	(118380, '2015-01-14', 1),
	(238280, '2000-02-01', 1),
	(1810296, '1999-01-13', 3),
	(298348, '2012-05-23', 2),
	(298348, '2011-03-10', 9),
	(67960, '2000-01-01', 2),
	(67960, '2000-04-10', 1)


select * from #TABLE1
select * from #TABLE2 where TABLE2CRITERIA in (1,2,3)

;with cte as
(
	select b.USERID, sum(case when b.TABLE2DATE < a.TABLE1DATE then 1 else 0 end) Total
	from #TABLE2 b inner join #TABLE1 a
	on b.USERID = a.TABLE1ID where b.TABLE2CRITERIA in (1,2,3)
	GROUP BY b.USERID
)
update a
set a.THECOUNT = b.Total
from #TABLE1 a
inner join 
cte b on a.TABLE1ID = b.USERID

select * from #TABLE1

Open in new window

ttist25

ASKER

Thanks again Ryan. I ran the above code and here is the part I was having trouble with:

For example, TABLE1ID 118380 should have the value '2' in THECOUNT for both rows because for row 1, the count of rows in the CTE that meet the criteria is 2. I think it's getting multiplied because there are multiple instances of 118380 in #TABLE1. Same thing for 67960.

Is there a way to use the other ID field in #TABLE1 to accomplish this?

Thanks so much for the help.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ttist25

ASKER

Awesome! Those are the results I would expect!

How did you make it work?

ttist25

ASKER

Oh I think I see!

You are grouping by userid and table1date:
GROUP BY b.USERID, a.TABLE1DATE

ttist25

ASKER

Thanks Ryan!