Showing posts with label col1from. Show all posts
Showing posts with label col1from. Show all posts

Wednesday, March 21, 2012

How to get the count of source records doing UPDATE with joined tables.

I have an sql like this in my stored procedure.
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
So, COL1 of T1 table is modified when T1 finds matching records in T2
table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
there are multiple matching records from T2. I want to make T1 table
be updated when there is exactly one matching record in T2.
Of course I can check the count of the matching records in T2 before
doing the above but T2 is actually from openquery interface(so dynamic
sql) to a remote server and I don't know exactly how I can get a
cursor with a dynamic sql.(I guess I should search this soon.)
If there is a way to find out the count of matching source records for
the above sql, it will help me a lot. Thanks..I think this should work
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
And (Select Count(*) From T2 Where T2.COL2=T1.COL2)=1
Dmitriy
"Yi, Dong-ryon" <feeva@.hanmail.net> wrote in message
news:bf8bb96c.0503131958.770f80ea@.posting.google.com...
>I have an sql like this in my stored procedure.
> UPDATE T1
> SET COL1 = T2.COL1
> FROM T1, T2
> WHERE T1.COL2=T2.COL2
> So, COL1 of T1 table is modified when T1 finds matching records in T2
> table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
> there are multiple matching records from T2. I want to make T1 table
> be updated when there is exactly one matching record in T2.
> Of course I can check the count of the matching records in T2 before
> doing the above but T2 is actually from openquery interface(so dynamic
> sql) to a remote server and I don't know exactly how I can get a
> cursor with a dynamic sql.(I guess I should search this soon.)
> If there is a way to find out the count of matching source records for
> the above sql, it will help me a lot. Thanks..|||On 13 Mar 2005 19:58:20 -0800, Yi, Dong-ryon wrote:

>I have an sql like this in my stored procedure.
>UPDATE T1
>SET COL1 = T2.COL1
>FROM T1, T2
>WHERE T1.COL2=T2.COL2
>So, COL1 of T1 table is modified when T1 finds matching records in T2
>table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
>there are multiple matching records from T2. I want to make T1 table
>be updated when there is exactly one matching record in T2.
>Of course I can check the count of the matching records in T2 before
>doing the above but T2 is actually from openquery interface(so dynamic
>sql) to a remote server and I don't know exactly how I can get a
>cursor with a dynamic sql.(I guess I should search this soon.)
>If there is a way to find out the count of matching source records for
>the above sql, it will help me a lot. Thanks..
Hi Yi,
As an alternative to the suggestion made by Dmitriy, here's a version
that refers to the T2 table in only one place:
UPDATE T1
SET Col1 = T2a.Col1
FROM T1
INNER JOIN (SELECT Col2, MIN(Col1) AS Col1
FROM T2
GROUP BY Col2
HAVING COUNT(*) = 1) AS T2a
ON T2a.Col2 = T1.Col2
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)