hi all.
I'm needing some help on updating whole column of a table (T1) according to a 2nd table T2(wich is a subselect from an openquery). I need to do this update every time a new record is added on T1.
Here are my table columns (& keys):

T1:
ID,T1_Col1, T1_Col2, T1_Col3, T1_Col4, T1_Col4, T1_Col5

T2: (wich is the return from the openquery)
T2_Col1, T2_Col2
this one returns more records than exists on T1

Here's what i need to do:

Set the T1_Col4 = T2_Col2
Where T1_Col1= T2_Col1
Every time an insert on T1 occurs (this should be part of a trigger)

My problems are with the use of the subselect from the openquery.

Can someboby help me on this??, I'm Stuck.

Regards,

I don't know what you mean with "subselect from the openquery". Could you explain this phrase a bit?

I don't know what you mean with "subselect from the openquery". Could you explain this phrase a bit?

Sorry about my bad english.

What i mean is:

This is my Select from a linked server.
SELECT A_Tag, A_Desc FROM openquery(RealTFIX,
'SELECT DISTINCT (ALCOBACA.A_TAG) ,ALCOBACA.A_Desc
FROM ALCOBACA
WHERE (ALCOBACA.A_NAME <> ''AR'') AND (ALCOBACA.A_NAME <> ''DR'') AND (ALCOBACA.A_NAME <> ''ETR'') AND (ALCOBACA.A_NAME <> ''TR'') AND
(ALCOBACA.A_NAME <> ''DI'') AND (ALCOBACA.A_NAME <> ''DA'') AND
(ALCOBACA.A_NAME <> ''PG'')') RT, hsttags Where RT.A_Tag = hsttags.Tag).

Which is what i called T2.

I want:
UPDATE T1
from T2
Setting the T1_Col4 = T2_Col2
Where T1_Col1= T2_Col1

Hope to be explicit.

Still working on this but without success.

Regards,

I solved it


thanks.

"openquery" is not standard SQL. So you should consult your product manuals.

The problem wasn't with openquery statment.
It was really to do with my mistake on update statement syntax.
Here's how i solved it.

update t1
set
Descr = t2.A_desc
from tags t1
join (SELECT * FROM
openquery(RealTFIX, 'SELECT DISTINCT (ALCOBACA.A_TAG) ,ALCOBACA.A_Desc FROM ALCOBACA ')RT, hsttags Where A_Tag = hsttags.Tag) t2 on
t2.a_tag=t1.tag

Your UPDATE... FROM... JOIN is not standard SQL, it's a proprietary Microsoft feature. The "UPDATE FROM" syntax also has a nasty bug-feature: if the join criteria is non-unique in the table being joined to then it won't cause an error message, it will silently update your data with some random value from the rows being joined - very dangerous in my opinion.

SQL Server 2008 supports MERGE, which IS standard SQL, is more powerful and generally more efficient than the equivalent UPDATE and doesn't suffer from the above problem. Always use MERGE and avoid UPDATE FROM wherever you can.