Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Incorrect syntax near '='.

Posted on 2014-09-27
3
Medium Priority
?
144 Views
Last Modified: 2014-09-27
I am trying to change occurences of 'NULL' in field ShippingAddressId to value 1
I get the following error:Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.
The code I have is below:
Thanks in advance for any help given.
USE [CorpWear265_Restore_TestAlt]
UPDATE dbo.[Order] SET ShippingAddressId =  CASE WHEN ShippingAddressId IS NULL THEN ShippingAddressId = 1
END
SELECT TOP 4000 [Id]
	  ,[OrderGuid]
      ,[CustomerId]
      ,[BillingAddressId]
      ,[ShippingAddressId]
      ,[OrderStatusId]
      ,[ShippingStatusId]
      ,[PaymentStatusId]
      ,[PaymentMethodSystemName]
      ,[CustomerCurrencyCode]
      ,[CurrencyRate]
      ,[CustomerTaxDisplayTypeId]
      ,[VatNumber]
      ,[OrderSubtotalInclTax]
      ,[OrderSubtotalExclTax]
      ,[OrderSubTotalDiscountInclTax]
      ,[OrderSubTotalDiscountExclTax]
      ,[OrderShippingInclTax]
      ,[OrderShippingExclTax]
      ,[PaymentMethodAdditionalFeeInclTax]
      ,[PaymentMethodAdditionalFeeExclTax]
      ,[TaxRates]
      ,[OrderTax]
      ,[OrderDiscount]
      ,[OrderTotal]
      ,[RefundedAmount]
      ,[RewardPointsWereAdded]
      ,[CheckoutAttributeDescription]
      ,[CheckoutAttributesXml]
      ,[CustomerLanguageId]
      ,[AffiliateId]
      ,[CustomerIp]
      ,[AllowStoringCreditCardNumber]
      ,[CardType]
      ,[CardName]
      ,[CardNumber]
      ,[MaskedCreditCardNumber]
      ,[CardCvv2]
      ,[CardExpirationMonth]
      ,[CardExpirationYear]
      ,[AuthorizationTransactionId]
      ,[AuthorizationTransactionCode]
      ,[AuthorizationTransactionResult]
      ,[CaptureTransactionId]
      ,[CaptureTransactionResult]
      ,[SubscriptionTransactionId]
      ,[PurchaseOrderNumber]
      ,[PaidDateUtc]
      ,[ShippingMethod]
      ,[ShippingRateComputationMethodSystemName]
      ,[Deleted]
      ,[CreatedOnUtc]
      ,[ShippedDateUtc]
      ,[DeliveryDateUtc]
      ,[OrderWeight]
      ,[TrackingNumber]
      ,[OrderEnquiry]
FROM [CorpWear265_Restore_TestAlt].[dbo].[Order]

Open in new window

0
Comment
Question by:homeshopper
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40347328
I would suggest:

UPDATE dbo.[Order]
SET ShippingAddressId =  1
WHERE  ShippingAddressId IS NULL
;

but the error was the case expression which should have been:

CASE WHEN ShippingAddressId IS NULL THEN 1 END
0
 

Author Comment

by:homeshopper
ID: 40347341
Thank you for your suggestion, that works.
Now I wanted to assign ShippingAddress to CustomerId value when it is Null.
I get the following error:The UPDATE statement conflicted with the FOREIGN KEY constraint
The new code I have is:
UPDATE dbo.[Order]
SET ShippingAddressId =  CustomerId
WHERE  ShippingAddressId IS NULL
Thanks for the help.
0
 

Author Closing Comment

by:homeshopper
ID: 40347352
Thankyou for the suggestion, I got the second query to work using the following:
ALTER TABLE dbo.[Order] NOCHECK CONSTRAINT ALL
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question