SQL UPDATE / Replace 1. simple update of values #2. Wildcard to replace LIKE values #3. Dealing with the common datatype 'ntext' error #4 Simple Update and replace. Find the oldstring, insert this new string... # 5. Clone values of column x to column y 6. Gotcha multiple updates you dummy

Update and or Replace.sql

--Updating values - simple
-- SET is the column name(s), and the new values
-- Include where statement to target a specific row
--1. simple update of values
Update dbo.dealers
SET latitude='34.000854', longitude='-81.099379'

WHERE dealer_code = '39054'  
***

--#2. Wildcard to replace LIKE values
--Update and replace all values of type X, with values of type Y.  
Update dbo.seo_template
SET main_copy_strong  = replace(main_copy_strong, '2011', '2014')
WHERE main_copy_strong LIKE '%2011%';

***
-- #3. Dealing with the common datatype 'ntext' error
--Argument data type ntext is invalid for argument 1 of replace function." Issue
--Failed because dealer_legal is datatype 'ntext'.  For some reason REPLACE not allowed with ntext.
--Per stackoverflowhttp://stackoverflow.com/questions/4341613/alternatives-to-replace-on-a-text-or-ntext-datatype */

UPDATE dbo.incentives_common
SET dealer_legal = CAST(REPLACE(CAST(dealer_legal as NVarchar(MAX)), 'offers.Must', 'offers.  Must') AS NText)
WHERE dealer_legal LIKE '%offers.Must%';

***
-- #4 Simple Update and replace. Find the oldstring, insert this new string...
-- Use Case : Good for when you have foreign key constraints, like the time I couldnt register an new email because my email address was already in the system.  
-- Was unable to DELETE due to so many foreign key constraints. The workaround was just to changed the email string. That way, went I went to register and the check 
-- was performed, there were not any records in the database that were 'testaccount@gmail.com' because I had changed it to 'testaccount@gmail.com9', thus a 
-- workaround to this validation, for testing purposes of course ( i have to create alot of registrations!!)
UPDATE _user
SET username = REPLACE(username, 'testaccount@gmail.com', 'testaccount@gmail.com9')

***
--# 5. Clone values of column x to column y
--Handy for setting all of column x to the same value as column y.  
UPDATE subscription
SET free_reminder_date = end_on

***
-- # Multiple Updates 'GOTCHA!'
--  If you use multiple SETS it will bomb, dummy.
-- eg SET payment_count = 5
--    SET paid_on = NULL
--  Instead, make it one SET statement with multiple changes, separated by commmas :
--C.  Warning for last charge decline
--CC3 Last charge to CC3 was declined...
UPDATE invoice
SET payment_failure_count = '1',
paid_on = NULL
WHERE invoice_id = '1'