• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

sql update

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
jgordin
Asked:
jgordin
  • 2
1 Solution
 
dqmqCommented:
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
 
jgordinAuthor Commented:
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
 
dqmqCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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