[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql update

Posted on 2007-08-10
3
Medium Priority
?
178 Views
Last Modified: 2010-03-19
i have three tables:

t1         t2     t3
===    ===  ===
c1       c1    c1
c2       c2    c2
c3    
c4
c5
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"
0
Comment
Question by:jgordin
  • 2
3 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 19674187
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'
0
 

Author Comment

by:jgordin
ID: 19674661
the proposed solution doesnt work. correction to the problem:

t1      t2     t3
===    ===  ===
c1       c1    c1
c2       c2    c2
c3    
c4
c5
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|
                   abc2

       

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|
                   abc2
0
 
LVL 42

Accepted Solution

by:
dqmq earned 1500 total points
ID: 19674745
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.

Consider:

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

Isn't t2.c2 in table2?

Consider:

   > 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]





0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore 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.
In this article I will describe the Copy Database Wizard 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.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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