Update Problem 1

[MDXer]

Here is an update statement that should work, but doesn't

Update dbo.Hotel_Ticket_Dimension 
Set
HTicket_Comp_Code = s.HTicket_Comp_Code,
HTicket_Folio_Num = s.HTicket_Folio_Num,
HTicket_Voucher_Num = s.HTicket_Voucher_Num,
HTicket_Post_Date = s.HTicket_Post_Date,
HTicket_Billing_Code = s.HTicket_Billing_Code,
HTicket_Comments = s.HTicket_Comments,
HTicket_Coupon_Number = s.HTicket_Coupon_Number,
HTicket_Department = s.HTicket_Department,
HTicket_First_Name = s.HTicket_First_Name,
HTicket_Freq_Traveler = s.HTicket_Freq_Traveler,
HTicket_Last_Name = s.HTicket_Last_Name,
HTicket_Late_Charge_Applied = s.HTicket_Late_Charge_Applied,
HTicket_Rev_Center = s.HTicket_Rev_Center,
HTicket_charge_group = s.HTicket_charge_group,
HTicket_Rev_Description = s.HTicket_Rev_Description,
HTicket_Room_Number = s.HTicket_Room_Number,
HTicket_Sequence_Key = s.HTicket_Sequence_Key,
HTicket_Server_Number = s.HTicket_Server_Number,
HTicket_Split_Ticket = s.HTicket_Split_Ticket,
HTicket_Status = s.HTicket_Status,
HTicket_Ticket_Sequence = s.HTicket_Ticket_Sequence,
HTicket_Type = s.HTicket_Type,
WH_RecDate = GetDate(),
WH_CRC1 = s.WH_CRC1,
WH_CRC2	= s.WH_CRC2
From dbo.zz_Hotel_Ticket_Dimension_stage s with(nolock)
	JOIN Hotel_Ticket_Dimension d with(nolock) ON d.HTicket_Comp_Code = s.HTicket_Comp_Code
						   and d.HTicket_Folio_Num = s.HTicket_Folio_Num
						   and d.HTicket_Voucher_Num = s.HTicket_Voucher_Num
						   and d.WH_CRC1 <> s.WH_CRC1 
						   and d.WH_CRC2 = s.WH_CRC2 
						   and d.WH_Current = 1

after executing it reports 2 rows modified but when the staged and dim records are compared they haven't been updated. I am totally at a loss.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[rac2]

Have you tried looking at which records will be updated using a SELECT query?

SELECT
       d.HTicket_Folio_Num
       , s.HTicket_Folio_Num
       , d.HTicket_Voucher_Num
       , s.HTicket_Voucher_Num
       , d.WH_CRC1
       , s.WH_CRC1
       , d.WH_CRC2
       , s.WH_CRC2
       , d.WH_Current
From dbo.zz_Hotel_Ticket_Dimension_stage s with(nolock)
    JOIN Hotel_Ticket_Dimension d with(nolock) ON d.HTicket_Comp_Code = s.HTicket_Comp_Code
                           and d.HTicket_Folio_Num = s.HTicket_Folio_Num
                           and d.HTicket_Voucher_Num = s.HTicket_Voucher_Num
                           and d.WH_CRC1 <> s.WH_CRC1 
                           and d.WH_CRC2 = s.WH_CRC2 
                           and d.WH_Current = 1

 

[MDXer]

yes and the records infact do not update. I would post the result set but it is some 27 columns wide and results in a miserable left right scroll

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[nigelrivett]

Why nolock?
Is and d.WH_CRC1 <> s.WH_CRC1 correct? If this is the PK should be "=".

Try doing a search for WH_RecDate. I suspect you aren't updating the rows you think you are.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tektipdjango]

maybe there a weird situation between the

set statement
WH_CRC1 = s.WH_CRC1,

and the join clause
d.WH_CRC1 <> s.WH_CRC1

both in the same query

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[donutman]

Is there an update trigger?
-Karl

 

[donutman]

How 'bout the fact that you don't have the Update table in the from clause...you aliased both sides of the join.
Try
Update d
set etc.

 

[MDXer]

This table is a dimension table in a data warehouse, the WH_CRC1 and WH_CRC2 Cyclic Redundancy Checksums which flag changes to enable Type 1 and Type 2 Slowly changing dimensions to occur. This update is for type 1 slowly changing dimensions. The same logic and structure works on approx 30-40 other tables.

in this instace
HTicket_Comp_Code
HTicket_Folio_Num
HTicket_Voucher_Num

form a unique record. the WH_CRC values are based upon a check sum generated based upon the values specified a either a type 1 or type 2.

The update is in proper format because I can replace s.wh_crc1 with '1' and it updates the record.

The idea that maybe it is updating the accoring to the wrong record is an idea that I will have to investigate.

Thanks to everyone for theri input.

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[MDXer]

Thanks and a star to Nigel. I placed to much faith in the information I was given about the source tables. I was told that HTicket_Comp_Code, HTicket_Folio_Num, HTicket_Voucher_Num made a record unique. Colser examination of the records proved this to be wrong. There where multiple records for Comp folio and voucher num, when HTicket_Post_Date was added the update worked as it should. I should have known better to trust the information I was given about the source system giving it's data quality and overall useability.

Thanks to everyone who helped point me in the right dimension.

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck