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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Very complex replace situation

Author  Topic 

raindear
Yak Posting Veteran

64 Posts

Posted - 2013-12-02 : 10:49:04
Hi

I 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 following

Description = £5 Off £25 Spend

Usage Text = £5 Off £25 Spend

Email Text = £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>

Set 2 contains the following

Description = 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 summary

I 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 field

So I need to replace the following in both sets

Description
UsageText
Email Text
PromotionalRuleData
PromotionDiscountData

What I need to is to somewhere in the SQL script I need to swap the Name values from set 1 to set 2

Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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....'
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 2013-12-02 : 12:13:57
Ok the latest I have is

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,etc...'




Can't seem to edit my old post but I am trying to execute this SQL script

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,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 as

CAST(PromotionRuleData as NVARCHAR(MAX)) = '<ArrayOfPromotionRuleBase ...
Go to Top of Page
   

- Advertisement -