CODEWITHSUNDEEP
mysqlsql-serverstored-procedures
Adu Rao
Adu Rao

Reputation: 101

Update table based on values of column in other 2 table

I have the following tables.

Table 1

Id | Data | Values 
1  | rfsd | 23
2  | fghf | 45
3  | rhhh | 23

Table 3

Id | Data | Values 
1  | rfsd | 23
2  | tfgy | 23

Table 2

Id | Fields | Counts
1  | 23     |   0 
2  | 45     |   0

As you can see, the Counts field is 0. I want to see 'Fields' column in table 2 and compare then with the 'Values' column in table 1 and table 3 and increment the count whenever the value is present in 'Values' field.

The final result should show like this

Table 2
Id | Fields | Counts
1  | 23     |   4
2  | 45     |   1

This will be the counts since 23 appear four times and 45 appear once in the 'Values' field of table 1 and table 3.

Can someone please let me know how to write a stored procedure for this.

I have implemented something like this. 

    WITH t1 AS (
              SELECT VALUES, COUNT(*) AS Count2 FROM Table1 GROUP BY VALUES)
UPDATE t2
SET t2.Counts = t1.Count2
FROM Table2 t2
JOIN t1 ON t2.Fields=t1.Values;

WITH t3 AS (SELECT VALUES, COUNT(*) AS Count3 FROM Table3 GROUP BY   VALUES)
UPDATE t2
SET t2.Counts = t3.Count2
FROM
Table2 t2
JOIN t3 ON t2.Fields=t3.Values;

When i use this stored procedure, it is updating t2 on basis of t1 table and then overwriting the table t2 by using t3 table.

I want t1 and t3 columns to be updated on t2 table at the same time. Any idea how to correct this stored procedure.

Upvotes: 0

Views: 50

Answers (2)

Chamika Goonetilaka
Chamika Goonetilaka

Reputation: 716

Hope this will help.

SELECT t2.Id, t2.Fields, COUNT(1) Counts
FROM Table2 t2
    LEFT JOIN Table1 t1 ON t2.Fields = t1.[Values]
    LEFT JOIN Table3 t3 ON t2.Fields = t3.[Values]
GROUP BY t2.Id, t2.Fields

If you need to update the Table2, use the following:-

WITH Results AS (
    SELECT t2.Id, COUNT(1) Counts
    FROM Table2 t2
       LEFT JOIN Table1 t1 ON t2.Fields = t1.[Values]
       LEFT JOIN Table3 t3 ON t2.Fields = t3.[Values]
    GROUP BY t2.Id
)
UPDATE t
SET Counts = r.Counts
FROM Table2 t
    JOIN Results r ON t.Id = r.Id;

Upvotes: 1

DVT
DVT

Reputation: 3127

You can use UNION ALL to group two tables 1 and 3 together (UNION ALL if you want disjoint union, UNION if you want to remove duplicate)

WITH t13 AS (
    SELECT Id, Data, [Values]
    FROM Table1
    UNION ALL
    SELECT Id, Data, [Values]
    FROM Table3   
), cte AS (
    SELECT 
        [Values], 
        COUNT(*) AS Count2 
    FROM t13
    GROUP BY
        [Values]
)
UPDATE t2
SET Counts = cte.Count2
FROM
    Table2 t2 JOIN cte ON t2.Fields = cte.[Values];

Upvotes: 1

Related Questions