Update Table on conditions

I need to update a table based on 4 conditions
1) If there are 3 0r more columns with the same conditions as below
2)if column A starts with "ABX"
3)and If column B is same for all 3 columns
4)If value of column c ='123'

then update  column D = True else false

for example

A                  B            C            D
ABX12            xyz            123            True
ABX13            xyz            123            True
ABX14            xyz            123            True
ABX15            xyz            124            False
ABX16            xyz            124            False

onlt first 3 colums have true bcos
there are 3 rows which start with ABX
and column B is same for all three and
column C value is '123' thats why D is True
if any of these conditions dosent satisfy the dis false
SvlssAsked:
Who is Participating?
 
pcelbaCommented:
You are slightly mixing columns and rows, so I suppose it should be:

1) If there are 3 or more ROWS with the same conditions as below
2) if column A starts with "ABX"
3) and If column B is same for all 3 ROWS
4) If value of column c ='123'
etc.

If c column is numeric you don§t need to use apostrophs and you should clarify data type of column D. I suppose it is bit.

create table MyTbl (A char(6), B char(5), C int, D bit)
insert into MyTbl VALUES ('ABX12', 'xyz', 123, null)
insert into MyTbl VALUES ('ABX13', 'xyz', 123, null)
insert into MyTbl VALUES ('ABX14', 'xyz', 123, null)
insert into MyTbl VALUES ('ABX15', 'xyz', 124, null)
insert into MyTbl VALUES ('ABX16', 'xyz', 124, null)


UPDATE MyTbl SET d = CASE WHEN t2.bCnt >= 3 THEN 1 ELSE 0 END
  FROM MyTbl t1
  LEFT JOIN (
       SELECT LEFT(A,3) a3, B, C, COUNT(*) bCnt 
         FROM MyTbl 
        WHERE c = '123' AND a LIKE 'ABX%' 
        GROUP BY LEFT(A,3), B, C 
       HAVING COUNT(*) >= 3) t2 
    ON LEFT(t1.A,3) = t2.a3 AND t1.B = t2.B AND t1.C = t2.C

Open in new window

0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should do
UPDATE ur_table
set D = 'True'
FROM ur_table t1 inner join (
SELECT A
FROM (
select A, count(A) over ( partition by LEFT(A, 3), B) cnt
From ur_table
Where LEFT(A, 3) = 'ABX'
AND C = 123 ) temp
WHERE cnt >= 3 ) t2
where t1.A = t2.A

Open in new window

0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Svlss,
   Have you tried out my query and any updates on that..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.