asked on

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

Raja Jegan R

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

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raja Jegan R

Svlss,
Have you tried out my query and any updates on that..