Update Table on conditions

Posted on 2009-12-22
Last Modified: 2012-05-08
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
Question by:Svlss
    LVL 57

    Expert Comment

    by:Raja Jegan R
    This should do
    UPDATE ur_table
    set D = 'True'
    FROM ur_table t1 inner join (
    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

    LVL 41

    Accepted Solution

    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'

    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

    LVL 57

    Expert Comment

    by:Raja Jegan R
       Have you tried out my query and any updates on that..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now