Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

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
0
Svlss
Asked:
Svlss
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
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 & ArchitectCommented:
Svlss,
   Have you tried out my query and any updates on that..
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now