Record not being updated twice

[NewbieWithSQL]

Here is the scenario that I have. I have a table "UpdateTest" (DDL at the end) that contains a few records.

UpdateID Data
-------------------
1 One
2 Two
3 Three

In another table "UpdateDelta" (DDL at the end), there are several records

UpdateDeltaID DeltaData UpdateDelta RowID
-----------------------------------------------
1 OneA U 1
2 TwoA I 2
NULL Four I 3
NULL Five I 4
NULL Six I 5
2 OneB U 6

I need to be able to process all three records that have a "U" in UpdateDelta into UpdateTest. When I run the update command it only shows updating 2 rows, when in it should update 3 rows. It only processes the first "U" record with UpdateDeltaID of 1.

This is the update statement that I'm using

update ut
set Data = ud.DeltaData

from UpdateTest ut
inner join UpdateDelta ud on ud.UpdateDeltaID = ut.UpdateID
where ud.UpdateDelta = 'U'

My results are

UpdateID Data
---------------------
1 OneA
2 TwoA

Results should be
UpdateID Data
---------------------
1 OneB
2 TwoA



I need to process the records in the order that they are in, which is why there is a rowid field, which is coming from the import process.

Any help is this matter is greatly appreciated.

-------------- DDL ------------


UpdateTest table DDL
CREATE TABLE [dbo].[UpdateTest](
[UpdateID] [int] NOT NULL,
[Data] [varchar](10) NOT NULL,
CONSTRAINT [PK_UpdateTest] PRIMARY KEY CLUSTERED
(
[UpdateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--- Insert data into UpdateTest
insert into UpdateTest(Data)
values('One')
insert into UpdateTest(Data)
values('Two')
insert into UpdateTest(Data)
values('Three')



CREATE TABLE [dbo].[UpdateDelta](
[UpdateDeltaID] [int] NULL,
[DeltaData] [varchar](10) NOT NULL,
[UpdateDelta] [char](1) NOT NULL,
[RowID] int identity(1,1) not null
) ON [PRIMARY]
Go

--- Insert of data into UpdateDelta
insert into UpdateDelta (UpdateDeltaID, DeltaData, UpdateDelta)
values (1,'OneA','U')

insert into UpdateDelta (UpdateDeltaID, DeltaData, UpdateDelta)
values (1,'OneB','U')

insert into UpdateDelta (UpdateDeltaID, DeltaData, UpdateDelta)
values (2,'TwoA','U')

insert into UpdateDelta (DeltaData, UpdateDelta)
values ('Four','I')

insert into UpdateDelta (DeltaData, UpdateDelta)
values ('Five','I')

insert into UpdateDelta (DeltaData, UpdateDelta)
values ('Six','I')

 

[gmmastros]

I need to process the records in the order that they are in, which is why there is a rowid field, which is coming from the import process.

Are you sure about this? What I mean is... is it important that the table be updated twice for the same row? I would approach this by getting just the data that needs to update and then updating just once.

The reason I ask is... sometimes there are triggers on tables which would make it important that you update the table twice.

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

 

[gmmastros]

Update  UpdateTest
Set     UpdateTest.Data = Delta.DeltaData
From    UpdateTest
        Inner Join (
          Select UpdateDelta.UpdateDeltaId, UpdateDelta.DeltaData
          From   UpdateDelta
                 Inner Join (
                   Select UpdateDeltaId, Max(RowId) As MaxRowId 
                   From   UpdateDelta 
                   Where  UpdateDelta = 'U'
                   Group By UpdateDeltaId
                   ) As B
                   On  UpdateDelta.UpdateDeltaId = B.UpdateDeltaId
                   And UpdateDelta.RowId = B.MaxRowId
          ) As Delta
          On UpdateTest.UpdateId = Delta.UpdateDeltaId

This is a relatively complex query, so I will explain the general approach I took.

1. I wanted to identify the row to get the data from, so I selected the updateid and max RowId from the update delta table for the U's.

2. Because I needed to use a group by, I couldn't return the actual data itself. This is why I join back to the update delta table using the row id in the join. This allows me to get the actual data.

3. I then joined to the table that needs updating. Since I only have one row for each update, and the data in that row matches the data in the highest RowId for the data, I know I am updating the table with the value that should get updated.

I hope this makes sense. If it doesn't, let me know and I will explain more.

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

 

[NewbieWithSQL]

So you are bypassing the first update record for ID 1. I can't bypass any records.

 

[SQLBill]

Why are you expecting there to be three updates? I only see two 'U' values in the UpdateDelta table. Your example shows you expect Two to be updated to TwoA, but in UpdateDelta that row shows an 'I', so it would be ignored.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

So you are bypassing the first update record for ID 1. I can't bypass any records.

Can you explain why?

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

 

[pjw001]

Even if you ignore the problem that only two rows have 'U' values, is there not another problem in that you are asking for one row to be updated from two rows in the second table.
I believe that in this situation SQLServer only does one update and it is not defined which row is used for the update.

 

[SQLBill]

The only reason I could see for you having to actually update a row multiple times would be if you are auditing the actions and have to record that those updates were made.

I think looping through the updatedelta table using rowid column would work for you. As you loop through each row, check to see if updatedelta is U and if it is, then do the update.

But if you are just doing each Update to do it, I think that is very unnecessary.

BTW- you may want to relook at your example, not only do you have only two U's, if you look at your example you really should be ending up with:
UpdateID Data
---------------------
1 OneA
2 OneB

OneB has an updatedeltaid of 2 not 1


-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875