Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
fungron
Starting Member
5 Posts |
Posted - 2013-02-20 : 13:44:28
|
Dear all This is my table structureuse sale3GOCREATE TABLE Product ( ProductCode nchar(10) not null,Description nvarchar(500),STDUnitCost numeric(11,4),STDUnitCostU nvarchar(5),STDUnitPrice numeric(11,4),STDUnitPriceU nvarchar(5),ValueBalance xml DEFAULT '<value />' NOT NULL,QtyBalance xml DEFAULT '<qty />' NOT NULL)INSERT INTO [Product]([ProductCode])VALUES('AB250')INSERT INTO [Product]([ProductCode])VALUES('AB100')UPDATE [Product]SET ValueBalance.modify('insert <Row><LocationCode id="ddd" /><Period id="122012" /><StockValue id="10" /><StockQty id="10" /></Row>as lastinto (/value)[1]')WHERE ProductCode='AB250'UPDATE [Product]SET ValueBalance.modify('insert <Row><LocationCode id="WHD" /><Period id="122012" /><StockValue id="20" /><StockQty id="10" /></Row>as lastinto (/value)[1]')WHERE ProductCode='AB250'UPDATE [Product]SET ValueBalance.modify('insert <Row><LocationCode id="TED" /><Period id="122012" /><StockValue id="10" /><StockQty id="10" /></Row>as lastinto (/value)[1]')WHERE ProductCode='AB100'UPDATE [Product]SET ValueBalance.modify('insert <Row><LocationCode id="WHD" /><Period id="122012" /><StockValue id="20" /><StockQty id="10" /></Row>as lastinto (/value)[1]')WHERE ProductCode='AB100'------------------------------What is the statement to update stockQty id=100where ProductCode='AB100' LocationCode id="WHD" Period id="122012"thanks |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-20 : 15:11:50
|
[code]UPDATE Product SET ValueBalance.modify(' replace value of (/value/Row[LocationCode/@id="WHD"][Period/@id="122012"]/StockQty/@id)[1] with "100"')WHERE ProductCode = 'AB100'[/code] |
|
|
fungron
Starting Member
5 Posts |
Posted - 2013-02-20 : 22:11:49
|
It works find, thank you so much |
|
|
|
|
|