sql update

Posted on 2007-08-10
Last Modified: 2010-03-19
i have three tables:

t1         t2     t3
===    ===  ===
c1       c1    c1
c2       c2    c2
i need to update t2.c2 = t3.c2
for the cases
    when t1.c1 = t2.c1 and t2.c2 = t3.c2 and t1.c4 = "ABC' and t1.c5 = "XYZ"
Question by:jgordin
    LVL 42

    Expert Comment

    Well you darn near wrote the SQL yourself:

    Update t2
    set t2.c2 = t3.c2
    from t1, t2, t3
    where t1.c1=t2.c1
    and t2.c2 = t3.c2
    and t1.c4='ABC'
    and t1.c5='XYZ'

    Author Comment

    the proposed solution doesnt work. correction to the problem:

    t1      t2     t3
    ===    ===  ===
    c1       c1    c1
    c2       c2    c2
    i need to update t2.c2 = t3.c2
    for the cases
        when t1.c1 = t2.c1 (t1.c1 will have two rows in t2.c1)

    t1                  t2                                    t3
    ==                 ============             ===========
     c1  c2  c3                             c1      c2   c3                      c1       c2
     -- ---------              --------------                   --------------
    abc1               abc1|      xyz1|                xyz1     mnp
    abc2              abc1|      xyz2|                xyz4
    abc2               abc2|     XYz3|


    t1.abc1, t1.abc2, and t1.abc3 is c1 above in table1

    t2.abc1, t2.abc1 ....         is c1 above in table2

    i need to update column2 (t2.c2) of table1 = with value in column 2 in table 3 in case when

    t1.c1 = t2.c1 and t2.c3 = t3.c1 (if t1.c1 = t2.c1 but t2.c3 doesnt have value in t3.c1 then

    no update) and t1.c3 = "abc' (additional condition).

    for above example i would expect MNP appear in t1.c2 (one

    t1                  t2                                  t3
    ==                 ============             ===========
     c1     c2        c3           c1   c2   c3                  c1       c2
     --                                        --
    abc1               abc      abc1| MNP xyz1|            xyz1     MNP
    abc2         ppp      abc1|         xyz2|            xyz4
    abc2               abc2|          xyzz3|
    LVL 42

    Accepted Solution

    No malice intended, but I almost refused to spend more time on this question because it looks like jibberish to me. Further, even your sentences are confusing.


       >i need to update column2 (t2.c2) of table1

    Isn't t2.c2 in table2?


       > in case when t1.c1 = t2.c1 and t2.c3 = t3.c1

    But t2, doesnt' have a c3!

    Nevertheless, I deciphered all that the best I could and came up with this. Please, if it does not work try to explain exactly what is wrong and be careful to get you tables/columns right.

    Update t1                       --I need to update column 2 of table1
    set t1.c2 = isnull(t3.c2,t1.c2) --with the value in c2 of table 3
                                    --but only when it has a value
    from t1, t2, t3
    where t1.c1=t2.c1    --in the case where t1.c1 = t2.c1
    and t2.c2 = t3.c1    --and t2.c3 = t3.c1 [I think you mean T2.C2]  
    and t1.c3='abc'      --and t1.c3 = "abc" [which I don't see 'abc' in your sample data]


    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

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    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

    19 Experts available now in Live!

    Get 1:1 Help Now