Update questions

1 Dec 2005 5:55 PM

J-T

I have an update statement as follow:

UPDATE T2 SET
      T2.field1 = T1.field1,
      T2.field2 =T1.field2
FROM table1 T1
      JOIN table2 T2 ON T2.TAG_ID = T1.TAG_ID
WHERE
                  T1.ERROR_STAT <> 1 AND
                 (T2.field1 <> T1.field1 OR
                  T2.field2 <> T1.field2 )

it works fine ,but the problem is that T2.field1 <> T1.field1 "AND"
T2.field2 = T1.field2 then because of the way the query is written T2.field2
gets updated or vise versa. I'd like to update only the field that has been
changed ,if both fields are changed then both.

Thanks

1 Dec 2005 6:01 PM

Stijn Verrept

J-T wrote:

> it works fine ,but the problem is that T2.field1 <> T1.field1 "AND"
> T2.field2 = T1.field2 then because of the way the query is written
> T2.field2 gets updated or vise versa. I'd like to update only the
> field that has been changed ,if both fields are changed then both.

Maybe it's better to do it in 2 seperate queries but here is how to do
it with one:

UPDATE T2 SET
     T2.field1 = case when T2.field1 <> T1.field1 then T1.field1 else
T2.field1 end,
      T2.field2 = case when T2.field2 <> T1.field2 then T1.field2 else
T2.field2 end
FROM table1 T1
      JOIN table2 T2 ON T2.TAG_ID = T1.TAG_ID
WHERE
                  T1.ERROR_STAT <> 1 AND
                 (T2.field1 <> T1.field1 OR
                  T2.field2 <> T1.field2 )

--

HTH,

Stijn Verrept.

1 Dec 2005 7:00 PM

J-T

Stijn,

>> I'd like to update "ONLY" the field that has been changed.

in your query T2.field1 gets again updated either with T1.field1 or bt
itself.It means the update takes palce but ,its data is different.I want the
update on T2.field1 not to take palce at all,when T2.field1 = T1.field1.

Thanks

Show quote

"Stijn Verrept" <st***@entrysoft.com> wrote in message
news:8-mdnTA7-_8YpxLeRVnysw@scarlet.biz...
> J-T wrote:
>
>> it works fine ,but the problem is that T2.field1 <> T1.field1 "AND"
>> T2.field2 = T1.field2 then because of the way the query is written
>> T2.field2 gets updated or vise versa. I'd like to update only the
>> field that has been changed ,if both fields are changed then both.
>
> Maybe it's better to do it in 2 seperate queries but here is how to do
> it with one:
>
> UPDATE T2 SET
>     T2.field1 = case when T2.field1 <> T1.field1 then T1.field1 else
> T2.field1 end,
>      T2.field2 = case when T2.field2 <> T1.field2 then T1.field2 else
> T2.field2 end
> FROM table1 T1
>      JOIN table2 T2 ON T2.TAG_ID = T1.TAG_ID
> WHERE
>                  T1.ERROR_STAT <> 1 AND
>                 (T2.field1 <> T1.field1 OR
>                  T2.field2 <> T1.field2 )
>
> --
>
> HTH,
>
> Stijn Verrept.

1 Dec 2005 10:27 PM

Stijn Verrept

J-T wrote:

> Stijn,
> > > I'd like to update "ONLY" the field that has been changed.

Then you need to use 2 queries.

--

Kind regards,

Stijn Verrept.