how to update a value in other table

[tinnu]

Hi guys.... suggestions needed

I have the following tables

Table : Temp3
--------------------------
Temp3ID Char1 Char2
1 x1 NULL
2 x1 312
3 x2 420
4 x2 NULL
5 x3 NULL
6 x3 889


Table : matter
------------------------
MatterID Temp3ID
900 1
901 3
902 4
904 5


Issue: I have to delete Temp3ID's from Temp3 table which are having their Char2 as NULL. i.e (I have to delete
Records from Temp3 table having Temp3ID's {1,4,5} But Before deleting them i have to update the Temp3ID values in the 'matter' table. I have to replace Temp3ID '1' with '2' and Temp3ID '4' with '3' and Temp3ID '5' with '6'

My resultant tables should look like these


Table : Temp3
-----------------------------------
Temp3ID Char1 Char2
2 x1 312
3 x2 420
6 x3 889


Table : matter
--------------------------
MatterID Temp3ID
900 2
901 3
902 3
903 6

I had solved this using CURSORS but due to performance issues i cannot use them. Is there any other way to
handle this.



Thanks in advance!!

 

[gmmastros]

I'm in a mood to explain this today, so if you are not interested in the explanation, then you should probably just jump to the bottom.

When I first read your question, my first thought was to see if I could get the correct results for the matter table because I realized that if I could get the results with a single query, then I know I can update the matter table with that same query.

So, the real challenger here is to get the correct results for the matter table.

In order for me to verify the results, I took your sample data and created table variables from them. This allows me to run the queries and verify the results.

To create the table variables...

Declare @Temp3 Table(Temp3ID INt, Char1 VarChar(20), Char2 VarChar(20))
Insert Into @Temp3 Values(1, 'x1', NULL )
Insert Into @Temp3 Values(2, 'x1', '312' )
Insert Into @Temp3 Values(3, 'x2', '420' )
Insert Into @Temp3 Values(4, 'x2', NULL )
Insert Into @Temp3 Values(5, 'x3', NULL)
Insert Into @Temp3 Values(6, 'x3', '889')


Declare @matter Table(MatterID Int, Temp3ID Int)
Insert Into @matter Values(900,1)
Insert Into @matter Values(901,3)
Insert Into @matter Values(902,4)
Insert Into @matter Values(904,5)

I noticed that the Char1 column was important for this to work properly, so I started by writing a query that would return the matter id and it's corresponding Char1 value.

Select *
From   @matter as matter
       Inner Join @Temp3 As Temp3
         On matter.Temp3Id = Temp3.Temp3Id

[tt][blue]-- Results
MatterID Temp3ID Char1 Char2
----------- ----------- -------------------- --------------------
900 1 x1 NULL
901 3 x2 420
902 4 x2 NULL
904 5 x3 NULL
[/blue][/tt]

The next thing I want to do is to get the largest Temp3ID that corresponds with the Char1 value, so I wrote this:

Select matter.MatterId, Temp3.Char1, A.Char2, A.Temp3Id
From   @matter as matter
       Inner Join @Temp3 As Temp3
         On matter.Temp3Id = Temp3.Temp3Id
       [!]Inner Join @Temp3 As A
         On Temp3.Char1 = A.Char1[/!]

[tt][blue]-- Results
MatterId Char1 Char2 Temp3Id
----------- ----- ----- -----------
900 x1 NULL 1
900 x1 312 2
901 x2 420 3
902 x2 420 3
901 x2 NULL 4
902 x2 NULL 4
904 x3 NULL 5
904 x3 889 6
[/blue][/tt]

Notice that we now more rows than we did before. This is to be expected. Clearly we are including rows where Char2 is NULL, which is not what we want, so let's add a filter condition for that.

Select matter.MatterId, Temp3.Char1, A.Char2, A.Temp3Id
From   @matter as matter
       Inner Join @Temp3 As Temp3
         On matter.Temp3Id = Temp3.Temp3Id
       Inner Join @Temp3 As A
         On Temp3.Char1 = A.Char1
[!]Where  A.Char2 Is Not NULL[/!]

[tt][blue]-- Results
MatterId Char1 Char2 Temp3Id
----------- ----- ----- -----------
900 x1 312 2
901 x2 420 3
902 x2 420 3
904 x3 889 6
[/blue][/tt]

At this point, it may appear as though we can just use the results because we have a column with MatterId and the correct value for Temp3Id. Well... we got lucky (I think) because there was only 1 match for each matter id, which may not be the case in the real data. I suspect we cannot rely on there being just one matching value, so let's add a little extra code to make sure.

Select matter.MatterId, [!]Max(A.Temp3Id) As Temp3Id[/!]
From   @matter as matter
       Inner Join @Temp3 As Temp3
         On matter.Temp3Id = Temp3.Temp3Id
       Inner Join @Temp3 As A
         On Temp3.Char1 = A.Char1
Where  A.Char2 Is Not NULL
[!]Group By matter.MatterId[/!]

[tt][blue]-- Results
MatterId Temp3Id
----------- -----------
900 2
901 3
902 3
904 6
[/blue][/tt]

Now that we have the correct results, we need to update the matter table. This is a little bit tricky because we cannot do a straight update since our results are obtained through the Max aggregate. To do this, we need to use a common table expression or derived table. The code below uses a derived table.

[!]Update matter
Set    matter.Temp3Id = B.Temp3ID
From   @matter As matter
       Inner Join ([/!]
         Select matter.MatterId, Max(A.Temp3Id) As Temp3Id
         From   @matter as matter
                Inner Join @Temp3 As Temp3
                  On matter.Temp3Id = Temp3.Temp3Id
                Inner Join @Temp3 As A
                  On Temp3.Char1 = A.Char1
         Where  A.Char2 Is Not NULL
         Group By matter.MatterId
[!]         ) As B
         On matter.MatterId = B.MatterId[/!]

Now, when we select the rows from the matter table, we get

[tt][blue]Select * From @matter
--Results
MatterID Temp3ID
----------- -----------
900 2
901 3
902 3
904 6
[/blue][/tt]

The only other thing to do is to remove the rows from Temp3

Delete From Temp3 Where Char2 Is NULL

Hopefully this makes sense. It's a relatively complicated query because it uses a self join, an aggregate, and a derived table. None of this concepts is especially difficult to understand, but using them together like this can be.

In your original question, you said the cursor approach was too slow. If you decide to implement my suggestion here, can you please let me know (approximately) how long it took with the cursor approach and also how long it takes with the approach I am suggesting. I'm curious to know.




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom