T-Sql Update based on count

Posted on 2012-09-18
Last Modified: 2012-09-26
ID      Value1
34      95
23      90

ID      Value1  Value2
34        91          89
23        88          86

ID       Role
34       P
34       J
23      P

Need an UPDATE statement that counts the ID's in Table3 and if the count is more than 1.  It should update Table1 with Value1 from Table2.  If the count is only one then it should UPDATE Table1 with Value2.

Update table1
set table1.Value1 = ?
Question by:moni81011
    LVL 12

    Accepted Solution

    set table1.Value1 =  case when (select count(*) from table3 as t3 where  ) = 1   then t2.value2
     when  (select count(*) from table3 as t3 where  ) > 1 then t2.value2
    from table1 t1  join table2 t2 on
    LVL 25

    Assisted Solution

    UPDATE table1
    SET Value1 = CASE WHEN v.cnt = 1 THEN table2.Value2 ELSE table2.value1 END
    FROM table1
    INNER JOIN table2 ON table1.ID = table2.ID
    INNER JOIN (SELECT id, count(*) cnt FROM table3 GROUP BY id) v ON table1.ID = v.ID

    Open in new window

    LVL 67

    Expert Comment

    The latter approach is to prefer, as it will perform much better, and is more straight-forward.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    754 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

    16 Experts available now in Live!

    Get 1:1 Help Now