Update table column based on column comparison from 2 table (SQL)
I have the following tables.
Table 1
Id | Values | Counts
1 | rock | 0
2 | tina | 0
3 | alex | 0
table 2
Id | Values
1 | rock
2 | alex
3 | alex
4 | rock
5 | rock
6 | tina
As you can see, table 1 contains values like stone, ooze and alex. These columns will always have unique values. The column of graphs should check the amount of "rock" in table 2 and update it in the column "Graphs". eg. the stone is shown 3 times in table 2. The columns for the breed should be equal to 3.
Likewise for other values. Can someone pls let me know how I can achieve this using SQL. This is how the final table should look like.
Table 1
Id | Values | Counts
1 | rock | 3
2 | tina | 1
3 | alex | 2
Any help is appreciated. I searched the web and couldn't find a possible solution for this scenario.
source to share
You can usually use JOIN
between two tables to update table 1 with the values from table2 (or if you are using bridge tables).
UPDATE t1
SET t1.dataColumn = t2.dataColumn
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.keyColumn = t2.keyColumn
However, when you use Aggregate functions (e.g. Count, Sum) you must use a subquery for the second table and JOIN that subquery
UPDATE t1
SET t1.Counts = sb.Counts
FROM Table1 AS t1
INNER JOIN (
SELECT [values], Counts = Count([values])
FROM Table2
GROUP BY [values]
) AS sb
ON t1.[values] = sb.[values]
Doing this on your tables gave me this:
SELECT * FROM Table1
id values counts
---- ------- -------
1 rock 3
2 tina 1
3 alex 2
One thing about your desk design; I generally recommend not using reserved / special / keywords when naming tables, columns, or other database objects. I also try to avoid using the common name id because it can get confusing when you start linking tables to each other, even idTable1 can make things a lot easier
source to share
SQL Server uses a correlated subquery:
update t1
set t1.Counts = (
select count(*)
from t2
where t2.[Values] = t1.[Values]
);
Demo version of the rexter: http://rextester.com/SBYNB72372
MySQL uses a correlated subquery:
update t1
set t1.Counts = (
select count(*)
from t2
where t2.`Values` = t1.`Values`
);
registry: http://rextester.com/DDDC21719
Although such a thing could be better calculated in view
instead of storing in a table t1
.
In SQL Server:
create view dbo.t1_with_counts as
select t1.Id, t1.[Values], count(t2.[Values]) as Counts
from t1
left join t2
on t1.[Values] = t2.[Values]
group by t1.Id, t1.[Values]
go
select *
from dbo.t1_with_counts;
In MySQL:
create view t1_with_counts as
select t1.Id, t1.`Values`, count(t2.`Values`) as Counts
from t1
left join t2
on t1.`Values` = t2.`Values`
group by t1.Id, t1.`Values`;
select *
from t1_with_counts;
source to share
I would question the wisdom of keeping track of an account in a table. This leads to poor structure and management of the relational database. Instead, I suggest deleting the column count
from Table 1. Then, when you need to see the counts you are using, follow these steps:
SELECT t1.ID, t1.VALUES, COUNT(t2.ID) AS VALUE_COUNT
FROM TABLE1 t1 LEFT JOIN TABLE2 t2 ON t1.VALUES = t2.VALUES
This results in a dynamically updated view of your data instead of a static view, which might become obsolete without you understanding.
source to share