Author |
Topic |
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-02 : 10:49:04
|
HiI have a SQL table that contains a table called Promotions. Here is the scenario...I have 1200 coupons in a table called promotions that are wrong out of 5000. Problem is they are in the wrong order. I need to swap them around.Set 1 contains the followingDescription = £5 Off £25 SpendUsage Text = £5 Off £25 SpendEmail Text = £5 Off £25 SpendPromotionRuleData = <ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-18T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-13T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule"><NumberOfUsesAllowed>1</NumberOfUsesAllowed></PromotionRuleBase><PromotionRuleBase xsi:type="MinimumCartAmountPromotionRule"><CartAmount>24.99</CartAmount></PromotionRuleBase></ArrayOfPromotionRuleBase>PromotionDiscountData = <ArrayOfPromotionDiscountBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionDiscountBase xsi:type="OrderPromotionDiscount"><DiscountType>Fixed</DiscountType><DiscountAmount>5.00</DiscountAmount></PromotionDiscountBase></ArrayOfPromotionDiscountBase>Set 2 contains the followingDescription = 50% Off Organic Raw Virgin Coconut Oil (480g) plus FREE Shipping Useage text = 50% Off Organic Raw Virgin Coconut Oil (480g) plus FREE Shipping Email Text = 50% Off Organic Raw Virgin Coconut Oil (480g) plus FREE Shipping PromotionalRuleData = <ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-18T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-13T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule"><NumberOfUsesAllowed>1</NumberOfUsesAllowed></PromotionRuleBase><PromotionRuleBase xsi:type="ProductIdPromotionRule"><ProductIds><int>55232</int></ProductIds><RequireQuantity>false</RequireQuantity><Quantity>1</Quantity><AndTogether>false</AndTogether></PromotionRuleBase></ArrayOfPromotionRuleBase>PromotionDiscountData = <ArrayOfPromotionDiscountBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionDiscountBase xsi:type="OrderItemPromotionDiscount"><DiscountType>Percentage</DiscountType><DiscountAmount>0.50</DiscountAmount></PromotionDiscountBase><PromotionDiscountBase xsi:type="ShippingPromotionDiscount"><DiscountType>Percentage</DiscountType><DiscountAmount>1</DiscountAmount></PromotionDiscountBase></ArrayOfPromotionDiscountBase>Each record has a Name value that I need to swap. So in summaryI have 600 records in set 1 and set 2 I need to swap the values of each set by a Name value.Any ideas? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-02 : 10:56:09
|
which field has Name value?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-02 : 10:59:47
|
It's a field called Name these are just other fields in the same table. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-02 : 11:03:16
|
quote: Originally posted by raindear It's a field called Name these are just other fields in the same table.
ok...so which fields you need to consider for that and whats the rule?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-02 : 11:07:46
|
Well I have 600 records that apply to the first set of data and 600 with the second that I need to swap around. So 1200 records, each one with a unique value in a Name fieldSo I need to replace the following in both setsDescriptionUsageTextEmail TextPromotionalRuleDataPromotionDiscountDataWhat I need to is to somewhere in the SQL script I need to swap the Name values from set 1 to set 2 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-02 : 11:10:36
|
So do you've any other field which identifies 600 records within a set uniquely?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-02 : 11:11:20
|
No just Name. I think I may have a way around this but any guidance would be great. |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-02 : 11:35:12
|
Ok got something like this but its not executing. Any ideas?UPDATE Promotions set Description = '£5 Off £25 Spend', UsageText = '£5 Off £25 Spend', EmailText = '£5 Off £25 Spend',PromotionRuleData = '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-18T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-13T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule"><NumberOfUsesAllowed>1</NumberOfUsesAllowed></PromotionRuleBase><PromotionRuleBase xsi:type="MinimumCartAmountPromotionRule"><CartAmount>24.99</CartAmount></PromotionRuleBase></ArrayOfPromotionRuleBase>',PromotionDiscountData = '<ArrayOfPromotionDiscountBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionDiscountBase xsi:type="OrderPromotionDiscount"><DiscountType>Fixed</DiscountType><DiscountAmount>5.00</DiscountAmount></PromotionDiscountBase></ArrayOfPromotionDiscountBase>', where Name = 'test1,test2,test3etc....' |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-02 : 12:13:57
|
Ok the latest I have isUPDATE Promotions set Description = '£5 Off £25 Spend', UsageText = '£5 Off £25 Spend',EmailText = '£5 Off £25 Spend',PromotionRuleData= '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-18T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-13T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule"><NumberOfUsesAllowed>1</NumberOfUsesAllowed> </PromotionRuleBase><PromotionRuleBase xsi:type="MinimumCartAmountPromotionRule"><CartAmount>24.99</CartAmount></PromotionRuleBase></ArrayOfPromotionRuleBase>',PromotionDiscountData = '<ArrayOfPromotionDiscountBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionDiscountBase xsi:type="OrderPromotionDiscount"><DiscountType>Fixed</DiscountType><DiscountAmount>5.00</DiscountAmount></PromotionDiscountBase></ArrayOfPromotionDiscountBase>'where Name = 'test1,test2,etc...' Can't seem to edit my old post but I am trying to execute this SQL scriptUPDATE Promotions set Description = '£5 Off £25 Spend', UsageText = '£5 Off £25 Spend',EmailText = '£5 Off £25 Spend',PromotionRuleData= '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-18T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-13T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule"><NumberOfUsesAllowed>1</NumberOfUsesAllowed> </PromotionRuleBase><PromotionRuleBase xsi:type="MinimumCartAmountPromotionRule"><CartAmount>24.99</CartAmount></PromotionRuleBase></ArrayOfPromotionRuleBase>',PromotionDiscountData = '<ArrayOfPromotionDiscountBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionDiscountBase xsi:type="OrderPromotionDiscount"><DiscountType>Fixed</DiscountType><DiscountAmount>5.00</DiscountAmount></PromotionDiscountBase></ArrayOfPromotionDiscountBase>'where Name = 'test1,test2,etc...'It comes back with this error Msg 402, Level 16, State 1, Line 1 The data types varchar and text are incompatible in the equal to operator.I try to use where CAST(PromotionRuleData as NVARCHAR(MAX))So the line reads asCAST(PromotionRuleData as NVARCHAR(MAX)) = '<ArrayOfPromotionRuleBase ... |
|
|
|