SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Very complex replace situation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raindear
Yak Posting Veteran

64 Posts

Posted - 12/02/2013 :  10:49:04  Show Profile  Reply with Quote
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?


Edited by - raindear on 12/02/2013 10:51:06

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/02/2013 :  10:56:09  Show Profile  Reply with Quote
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 - 12/02/2013 :  10:59:47  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/02/2013 :  11:03:16  Show Profile  Reply with Quote
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 - 12/02/2013 :  11:07:46  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/02/2013 :  11:10:36  Show Profile  Reply with Quote
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 - 12/02/2013 :  11:11:20  Show Profile  Reply with Quote
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 - 12/02/2013 :  11:35:12  Show Profile  Reply with Quote
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....'

Edited by - raindear on 12/02/2013 11:36:53
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 12/02/2013 :  12:13:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000