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

# 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
• 2
1 Solution

Commented:
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 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

Commented:
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.