newbie tsql syntax: update one table from another ...

23 Dec 2005 4:43 AM

Chris Smith

I have two tables, t1 and t2 for lack of a more imaginative mood.

t1 -> field1, field2, field3, field4
t2 -> fieldA, fieldB, fieldC, fieldD

I want to set t1.field2=t2.fieldB where t1.field3=t2.fieldC

I have tried and tried, but no luck. This has GOT to be a whole lot simpler
than I am making it out to be.

Any help would be appreciated.

23 Dec 2005 5:15 AM

Steve Kass

update t1 set
field2 = (
    select fieldB from t2
    where t2.fieldC = t1.field3
)

If not all rows should be updated, then use either

-- T-SQL proprietary UPDATE .. FROM
update t1 set
field2 = f2.fieldB
from t2 join t1
on t1.field3 = t2.fieldC

or

-- Standard SQL
update t1 set
field2 = (
    select fieldB from t2
    where t2.fieldC = t1.field3
) and exists (
select * from t2
where t2.fieldC = t1.field3
)

Steve Kass
Drew University

Chris Smith wrote:

Show quote

>I have two tables, t1 and t2 for lack of a more imaginative mood.
>
>t1 -> field1, field2, field3, field4
>t2 -> fieldA, fieldB, fieldC, fieldD
>
>I want to set t1.field2=t2.fieldB where t1.field3=t2.fieldC
>
>I have tried and tried, but no luck. This has GOT to be a whole lot simpler
>than I am making it out to be.
>
>Any help would be appreciated.
>
>
>
>

23 Dec 2005 5:16 AM

SQL novice

Check this out...

Update t1 set field1 = t2.fieldB from t1 t1A JOIN t2 on
t1A.field3=t2.fieldC

23 Dec 2005 5:24 AM

Anith Sen

It depends on the whether the column or set of columns that are to be
matched are unique or not. If the values in column fieldC is unique in the
table t2, you could do:

UPDATE t1
   SET field2 = t2.fieldB
FROM t2
WHERE t1.field3 = t2.fieldC ;

Otherwise, you could do:

UPDATE t1
   SET field2 = ( SELECT t2.fieldB FROM t2
                   WHERE t1.field3 = t2.fieldC )
WHERE EXISTS   ( SELECT * FROM t2
                   WHERE t1.field3 = t2.fieldC ) ;

--
Anith

23 Dec 2005 5:25 AM

SriSamp

Try this:

UPDATE t1 SET
    t1.field2 = t2.fieldB
FROM
    t1
    INNER JOIN t2 ON t1.field3 = t2.fieldC

--
HTH,
SriSamp
Email: sris***@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Chris Smith" <chris.sm***@shaw.ca> wrote in message

news:ggLqf.171411$Gd6.13373@pd7tw3no...
Show quote

>I have two tables, t1 and t2 for lack of a more imaginative mood.
>
> t1 -> field1, field2, field3, field4
> t2 -> fieldA, fieldB, fieldC, fieldD
>
> I want to set t1.field2=t2.fieldB where t1.field3=t2.fieldC
>
> I have tried and tried, but no luck. This has GOT to be a whole lot
> simpler than I am making it out to be.
>
> Any help would be appreciated.
>

23 Dec 2005 5:46 PM

Chris Smith

Thank you.

I am finding that TSQL syntax is just different enough from what I am used
to with Oracle that it is causing me a bit of grief and frustration. Thank
goodness for yourself and the newsgroups, or I would have a heck of a lot
less hair.

I appreciate the help.

Show quote

"SriSamp" <ssamp***@sct.co.in> wrote in message
news:uV2wuC4BGHA.2320@TK2MSFTNGP11.phx.gbl...
> Try this:
>
> UPDATE t1 SET
>    t1.field2 = t2.fieldB
> FROM
>    t1
>    INNER JOIN t2 ON t1.field3 = t2.fieldC
> --
> HTH,
> SriSamp
> Email: sris***@gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
>
> "Chris Smith" <chris.sm***@shaw.ca> wrote in message
> news:ggLqf.171411$Gd6.13373@pd7tw3no...
>>I have two tables, t1 and t2 for lack of a more imaginative mood.
>>
>> t1 -> field1, field2, field3, field4
>> t2 -> fieldA, fieldB, fieldC, fieldD
>>
>> I want to set t1.field2=t2.fieldB where t1.field3=t2.fieldC
>>
>> I have tried and tried, but no luck. This has GOT to be a whole lot
>> simpler than I am making it out to be.
>>
>> Any help would be appreciated.
>>
>
>