November 19, 2008 at 1:02 am
How can I do this using t-sql?
I have two tables, t1 and t2,
t1:
c1 c2 c3 c4
------------------------------------------
3 4 5 NULL
3 6 5 NULL
4 7 6 NULL
5 4 7 NULL
t2:
d1 d2 d3
--------------------------
3 6 5
5 9 7
5 9 8
I want to set c4 = 1 for rows in t1 whose first column matches the first column of t2
and at the same time the ordered pair (t1.c2,t1.c3), from the same row, differs from (t2.d2,t2.d3).
By differs I mean that at least one element of the pair differs.
Here's the end result I'm looking for:
t1:
c1 c2 c3 c4
------------------------------------------
3 4 5 1 (Comment A)
3 6 5 NULL (Comment B)
4 7 6 NULL (Comment C)
5 4 7 1 (Comment D)
Comment A: For the first row of t1, c1 = 3. That column matches d1 in the first row of t2. But for that row pairing (c2, c3) = (4,5) while (d2, d3) = (6.5). The first columns match but at least
one of the other columns don't.
Comment B: The second row of t1 matches the second row of t2. While the first columns
match so do the remaining pairs.
Comment C: The third row of t1 has c1 = 4. This value does not appear in t2.d1 at all so this case is ignored.
Comment D: The fourth row of t1 has c1 = 5. That matches the first column of two rows in t2. But (4,7) <> (9,7) so the "goal" has been met.
TIA,
Barkingdog
>>>>>> Table Generation scripts
CREATE TABLE [dbo].[t1](
[c1] [nchar](10) NULL,
[c2] [nchar](10) NULL,
[c3] [nchar](10) NULL,
[c4] [nchar](10) NULL
)
insert into t1 values(3,4,5, NULL)
insert into t1 values(3,6,5, NULL)
insert into t1 values(4,7,6, NULL)
insert into t1 values(5,4,7, NULL)
CREATE TABLE [dbo].[t2](
[d1] [nchar](10) NULL,
[d2] [nchar](10) NULL,
[d3] [nchar](10) NULL
)
insert into t2 values(3,6,5)
insert into t2 values(5,9,7)
insert into t2 values(5,9,8)
November 19, 2008 at 1:22 am
The code bellow will update the records that you wanted to with you example, but I’m not sure that it is what you want. In your explanation, you wrote that you want the first record to be updated. How do you define the first record? With my code all records that meet the criteria will be updated. You can take it as a base query and modify it according to your needs.
update t1
set c4 = 1
from t1 inner join t2 on t1.c1 = t2.d1
where t1.c1 <> t2.d1 or t1.c2 <> t2.d2
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 19, 2008 at 2:05 am
I think that will do it!
Barkingdog
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply