CODEWITHSUNDEEP
sqlsql-server
Tony
Tony

Reputation: 55

To update a column by checking the value from another column in a different table

I am trying to update flag in my main table based on the flag in another common table.Both are related with the Foreign Key relationship. But the problem is the flag in another common table is either 0 or 1. So, it should update the flag in the main table as 1 only if all the values for a particular FK is 1.

Suppose that there are 2 tables listed below. XYZ and ABC. Both are related to each other through Foreign Key. XYZ:

XYZID   Posted
1       0
2       0
3       0
4       0

ABC:

ABCID   XYZID   IsPosted
1       1       1
2       1       1
3       2       0
4       2       0
5       2       0
6       3       1
7       3       0
8       4       0
9       4       0
10      4       1

If you see for XYZID in ABC table the Isposted value is 1 for both. I want that value to be updated in the Posted as 1 of XYZ main table for XYZID 1. But if you look at XYZID value 3 in ABC table for IsPosted then it is 0 and 1. So for XYZID value 3 the Posted value should not be updated in the XYZ table as 1. In general, if all the foreign key value has the IsPosted as 1 then only it should be updated as 1 in the Posted column of XYZ table. If it is 0 or 1 then it should not update in the XYZ table.

I thought of using group by or cursor. But don't know how to start on this.

If anyone can help me in this then would be helpful. It is pretty simple but I am not getting the idea to start on this. Any help would be appreciated.

Upvotes: 0

Views: 429

Answers (3)

sticky bit
sticky bit

Reputation: 37472

Assuming there can only be 0 or 1, one way is to use a correlated subquery getting the minimum isposted for an xyzid.

UPDATE main_table
       SET posted = (SELECT min(another_common_table.isposted)
                            FROM another_common_table
                            WHERE another_common_table.xyzid = main_table.xyzid);

If there is a 0 the minimum will be 0. If there's only 1s it'll be 1.

Upvotes: 1

Forty3
Forty3

Reputation: 2229

Try the following:

UPDATE [a]
SET    a.[Posted] = [b].[IsPosted]
FROM   [a]
       INNER JOIN (SELECT [xyzid],
                          [IsPosted] = MIN(Cast([IsPosted] AS INT))
                   FROM
                     [b]
                   GROUP  BY
                    [xyzid]
                   HAVING
                    MIN(Cast([IsPosted] AS INT)) = 1) [b]
               ON [a].[xyzid] = [b].[xyzid]

Essentially, the inner query returns only those entries from table B with all 1 values and then updates the A table based on the FK join.

There may be more efficient queries AND this will re-update previously updated A.Posted values and will NOT un-update A.Posted if anything in table B is marked as IsPosted = 0.

Upvotes: 0

forpas
forpas

Reputation: 164064

Update the table by joining a subquery that groups by xyzid the table abc and sets the condition in the having clause:

update t
set posted = 1  
from xyz t inner join (
  select xyzid from abc
  group by xyzid
  having sum(case when isposted = 0 then 1 else 0 end) = 0
) a on a.xyzid = t.xyzid

The condition in the having clause could also be written:

having sum(abs(isposted - 1)) = 0

See the demo.
Results:

> XYZID | Posted
> ----: | -----:
>     1 |      1
>     2 |      0
>     3 |      0
>     4 |      0

Upvotes: 1

Related Questions