Mental Block on a simple query

[thegeezza]

Afternoon,

I want to perform an update to a record on the left hand side of my join, for the number of times the join condition is true. For exmaple:

update A
	set match_counter = match_counter + 1
from TABLEA A
	left join TABLEB  B on
	A.postcode = B.postcode

However, the code above obviously only updates each record where the condition is true once.

I hope this makes sense. Any assistance is greatly appreciated.

TheGeezza

 

[hmckillop]

do you want an incremental update i.e.

declare @vTempInt bigint
set @vTempInt  = 0
update A
    set @vTempInt = @vTempInt + 1
        ,match_counter = match_counter + @vTempInt
from TABLEA A
    left join TABLEB  B on
    A.postcode = B.postcode

I am not 100% sure what you are asking for, maybe a sample data set of what is happening and what you want to happen might help.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[thegeezza]

Yes that is exactly what I am looking for thank you.

Are you able to explain the logic behind this part?

set @vTempInt = @vTempInt + 1
        ,match_counter = match_counter + @vTempInt

For example, why is match_counter only updated once in my code, yet when you used a variable, the variable is updated numerous times, which then in turn is applied to match_counter.

Thanks,
TheGeezza

 

[thegeezza]

Oops.
Actually on my second look this, the code simply creates an incremental counter and applies this to the file.

What I am looking to do is create a count for the number of times a record on the left is joined to the right table. So essentially a counter for the one to many relationship, counting the many side, for each record on the left.

Regards,
TheGeezza

 

[hmckillop]

Effectively what is happening with your query is that each row is being set to the same value each time.
So if all your rows of data for the column match_counter equalled 1 prior to starting, then the 1st row would be equal to match_counter +1, the second row would be equal to match_counter + 1 etc (so each row is processed individually and on its own).
With the variable, the variable is incremented as each row is processed, and as such it contains the running count. Then using this to add against match_counter allows you to effectively have an incremental count.
Sorry I cant explain this better.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

Can you give sample data and I should be able to get something working


"I'm living so far beyond my income that we may almost be said to be living apart

 

[thegeezza]

Sure Thing.

TableA
Add1	                postcode         match_counter
27 Pettens Close	AB23 8WZ

TableB
Add1	                postcode
27 Pettens Close	AB23 8WZ
27 Pettens Close	AB23 8WZ
27 Pettens Close	AB23 8WZ

So match counter would be three for the correct query output/udpate value

 

[hmckillop]

It should then be

UPDATE TableA
SET Match_counter = (SELECT COUNT(*) FROM TableB Where TableB.Postcode = A.Postcode)
FROM TableA

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

sorry slight typo

UPDATE TableA
SET Match_counter = (SELECT COUNT(*) FROM TableB Where TableB.Postcode = tableA.Postcode)
FROM TableA

"I'm living so far beyond my income that we may almost be said to be living apart

 

[thegeezza]

Would that not then apply the same count to every record in the left/parent table?

There are other records, both on the left, with corresponding groups of matches on the right, in the full data sets.

 

[hmckillop]

Apolgies, I was working on the data set you provided and yes it would do as you have stated.
Are you basically saying that the parent table has multiple entries for the same data?
WHat you then need is a case statement to reset the value. Will post in a few mins, need to code this one.




"I'm living so far beyond my income that we may almost be said to be living apart

 

[thegeezza]

Data should look like the below tables. Table A being the left table.

TableA
Add1                    postcode         match_counter
27 Pettens Close    AB23 8WZ             3
61 Malden Hill      KT3 4D2              2
57 Melrose          W4 2PD               1

TableB
Add1                    postcode
27 Pettens Close    AB23 8WZ
27 Pettens Close    AB23 8WZ
27 Pettens Close    AB23 8WZ
61 Malden Hill      KT3 4D2
61 Malden Hill      KT3 4D2
57 Melrose          W4 2PD

 

[druer]

In general, the best/easiest/fastest way to get help is to provide some examples/illustrations, such as the table designs and some sample data, and then your expected results, otherwise we are all left guessing.

I think what you are trying to say is that you have a ONE to MANY relationship. Such as Parts and Customers that bought the Part. And you are trying to update a field in the Parts table to reflect how many customers have purchased that part. So if you have Part 1 purchased 10 times, you want to update the field to 10, and if Part 2 was purchased 20 times you want to update the field to 20. Is that the jist of what you are looking to do?

 

[hmckillop]

Ok here is the scenario if the parent table has two entries for the below data set

TableA
Add1                    postcode         match_counter
27 Pettens Close    AB23 8WZ
27 Pettens Close    AB23 8WZ

TableB
Add1                    postcode
27 Pettens Close    AB23 8WZ
27 Pettens Close    AB23 8WZ
27 Pettens Close    AB23 8WZ

What should the output be from this?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

Ok if what you want is what you have posted then use the query I last posted.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[thegeezza]

Druer, you are correct.

hmckillop the output for table A would update to:

TableA
Add1                    postcode         match_counter
27 Pettens Close    AB23 8WZ                 3
27 Pettens Close    AB23 8WZ                 3

Once more, everyones help is most appreciated.

 

[hmckillop]

e.g. sample data

tablea
27 Pettens Close	AB23 8WZ	0
1 another address	AB1 1WZ	0
2 another address	AB2 1WZ	0

tableb
27 Pettens Close	AB23 8WZ
27 Pettens Close	AB23 8WZ
27 Pettens Close	AB23 8WZ
2 another address	AB2 1WZ
2 another address	AB2 1WZ
2 another address	AB2 1WZ
2 another address	AB2 1WZ
2 another address	AB2 1WZ
1 another address	AB1 1WZ
1 another address	AB1 1WZ
1 another address	AB1 1WZ
1 another address	AB1 1WZ
1 another address	AB1 1WZ
1 another address	AB1 1WZ
1 another address	AB1 1WZ
1 another address	AB1 1WZ

output of table a using query

UPDATE TableA
SET Match_counter = (SELECT COUNT(*) FROM TableB Where TableB.Postcode = tableA.Postcode)
FROM TableA

is

27 Pettens Close	AB23 8WZ	3
1 another address	AB1 1WZ	8
2 another address	AB2 1WZ	5

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

If you not the inner join condition on the count statement - tableB.postcode = tableA.postcode
This means that it will only count based on this condition and as you can see I think this gives what you want.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[druer]

hm's query is just what you need.

Is there are a specific reason though that you are trying to do this (like DataWarehousing). Because of course you realize that the number will be incorrect within seconds/minutes of completing if the second table is something that is live. You could alternatively use the query in a VIEW for the table instead so that it will always return the live results:

CREATE VIEW myViewOfTable1 AS
select field1, field2, field3, (SELECT COUNT(*) FROM TableB Where TableB.Postcode = PostCode) as MatchCount
from TableA

Then you can simply query against the View to get actual live totals any time you want to. And you would be able to limit the results with any where clauses against TableA as well.

Just a thought

 

[thegeezza]

Druer,

A query is sufficient in this scenario thanks.

FYI, the two data sets are static and are part of a data processing project. So one file is the universe and the other is a client data set. The data is being loosley matched at different levels to approximate project results.

The reason for this query is to count the number of matches at at a given level. You see, depending on the matching rule used, if only one match is available then it can be deemed acceptable.

It is all quite long/detailed and so I will not get bogged down with details.

Once more, thank you all for your input.

Have a nice day.