Solved

Incorrect syntax near '='.

Posted on 2014-09-27
3
135 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 48

Accepted Solution

by:
PortletPaul earned 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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