Formatting an UPDATE query with multiple items in SET phrase

Answers

• Options

  RTSJohn Posts: 7 Bronze 1

  October 25, 2019 8:36PM

  Addendum: this error seems to be happening a lot, not just on update queries.  Every time, it's "Sequence contains more than one element."

  0 · Share on Twitter
• Options

  Tianjiao_Li Redgate › Posts: 684 Rose Gold 5

  October 28, 2019 2:16PM

  So sorry to hear you have this issue.
  
  Can you share your format style file since it doesn't error with the default one for me?
  
  Thanks.

  Kind regards
  
  Tianjiao Li | Redgate Software
  Have you visited our Help Center?
  

  0 · Share on Twitter
• Options

  RTSJohn Posts: 7 Bronze 1

  October 29, 2019 8:57PM

  Here's the file I'm using.
  
  

  <?xml version="1.0" encoding="utf-8"?>

  <LayoutOptions xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Format.Engine.Formatting.Options">

    <AlignCaseElseToWhen>true</AlignCaseElseToWhen>

    <AlignToTab>false</AlignToTab>

    <BetweenAndAlignment>ToBetween</BetweenAndAlignment>

    <BooleanOperatorAlignment>AsPartOfList</BooleanOperatorAlignment>

    <BooleanOperatorBreakType>ChopIfLong</BooleanOperatorBreakType>

    <BreakAssignmentIfLong>true</BreakAssignmentIfLong>

    <BreakOnConstraints>true</BreakOnConstraints>

    <BuiltInDataTypeCasing>Uppercase</BuiltInDataTypeCasing>

    <BuiltInFunctionCasing>Uppercase</BuiltInFunctionCasing>

    <CaseEndAlignment>ToCase</CaseEndAlignment>

    <CaseExpressionAlignment>Tabbed</CaseExpressionAlignment>

    <CaseWhenElseAlignment>Tabbed</CaseWhenElseAlignment>

    <ClauseAlignment>ToStatement</ClauseAlignment>

    <ClauseIndentation>0</ClauseIndentation>

    <ClosingParenthesisAlignment>ToOpeningBracket</ClosingParenthesisAlignment>

    <CollapseCaseExpressionIfShort>false</CollapseCaseExpressionIfShort>

    <CollapseCaseIfShortCharacterCount>75</CollapseCaseIfShortCharacterCount>

    <CollapseIfShortControlFlowCharacterCount>78</CollapseIfShortControlFlowCharacterCount>

    <CollapseIfShortDdlCharacterCount>75</CollapseIfShortDdlCharacterCount>

    <CollapseIfShortDmlCharacterCount>78</CollapseIfShortDmlCharacterCount>

    <CollapseIfShortParenthesesContentsCharacterCount>75</CollapseIfShortParenthesesContentsCharacterCount>

    <CollapseIfShortSubqueryCharacterCount>78</CollapseIfShortSubqueryCharacterCount>

    <CollapseShortControlFlowStatements>true</CollapseShortControlFlowStatements>

    <CollapseShortDdlStatements>true</CollapseShortDdlStatements>

    <CollapseShortDmlStatements>true</CollapseShortDmlStatements>

    <CollapseShortParenthesesContents>true</CollapseShortParenthesesContents>

    <CollapseShortSubqueries>true</CollapseShortSubqueries>

    <CommaAlignment>ToList</CommaAlignment>

    <ConstraintColumnsBreakType>ChopIfLongOrMultipleItems</ConstraintColumnsBreakType>

    <CreateAlterClosingParenthesisAlignment>ToOpeningBracket</CreateAlterClosingParenthesisAlignment>

    <CreateAlterIndentParenthesisedExpression>true</CreateAlterIndentParenthesisedExpression>

    <CreateAlterOpeningParenthesisAlignment>ToStatement</CreateAlterOpeningParenthesisAlignment>

    <CreateAlterOpeningParenthesisBreakType>ChopAlways</CreateAlterOpeningParenthesisBreakType>

    <CreateAlterParenthesisLayout>ExpandedToStatement</CreateAlterParenthesisLayout>

    <CreateAlterParenthesisedExpressionBreakType>ChopAlways</CreateAlterParenthesisedExpressionBreakType>

    <CreateAlterPlaceClosingParenthesisOnNewLine>true</CreateAlterPlaceClosingParenthesisOnNewLine>

    <CreateTableBreakBeforeFirstListItem>true</CreateTableBreakBeforeFirstListItem>

    <CteAsAlignment>ToStatement</CteAsAlignment>

    <CteColumnsOpeningParenthesisAlignment>ToStatement</CteColumnsOpeningParenthesisAlignment>

    <CteColumnsOpeningParenthesisBreakType>Never</CteColumnsOpeningParenthesisBreakType>

    <CteContentsOpeningParenthesisAlignment>ToStatement</CteContentsOpeningParenthesisAlignment>

    <CteContentsOpeningParenthesisBreakType>Never</CteContentsOpeningParenthesisBreakType>

    <CteContentsParenthesisLayout>CompactSimple</CteContentsParenthesisLayout>

    <CteContentsParenthesisedExpressionAlignment>ToStartOfOpeningBracket</CteContentsParenthesisedExpressionAlignment>

    <CteContentsParenthesisedExpressionBreakType>Never</CteContentsParenthesisedExpressionBreakType>

    <FirstCreateAlterDefintionBreakType>ChopIfLongOrMultipleItems</FirstCreateAlterDefintionBreakType>

    <FirstCreateProcedureParamBreakType>ChopIfLongOrMultipleItems</FirstCreateProcedureParamBreakType>

    <FirstListItemBreakType>ChopIfLongOrMultipleItems</FirstListItemBreakType>

    <FromClauseFirstItemBreakType>Never</FromClauseFirstItemBreakType>

    <FunctionArgumentsBreakType>ChopIfLong</FunctionArgumentsBreakType>

    <GlobalVariablesCasing>LeaveAsIs</GlobalVariablesCasing>

    <GroupByOrderByFirstItemBreakType>Never</GroupByOrderByFirstItemBreakType>

    <InValuesFirstItemBreakType>SimpleWrap</InValuesFirstItemBreakType>

    <InValuesOpeningParenthesisAlignment>ToStatement</InValuesOpeningParenthesisAlignment>

    <InValuesOpeningParenthesisBreakType>Never</InValuesOpeningParenthesisBreakType>

    <InValuesSubsequentListItemsBreakType>SimpleWrap</InValuesSubsequentListItemsBreakType>

    <IndentBeginEndKeywords>false</IndentBeginEndKeywords>

    <IndentBlockContents>true</IndentBlockContents>

    <IndentCteContents>false</IndentCteContents>

    <IndentCteName>false</IndentCteName>

    <IndentDdlClauses>false</IndentDdlClauses>

    <IndentInsertColumnListsParenthesisedExpression>true</IndentInsertColumnListsParenthesisedExpression>

    <IndentInsertValuesListsParenthesisedExpression>true</IndentInsertValuesListsParenthesisedExpression>

    <IndentJoinTable>false</IndentJoinTable>

    <IndentListItems>true</IndentListItems>

    <IndentParenthesisedExpression>true</IndentParenthesisedExpression>

    <InsertColumnListParenthesisLayout>ExpandedToStatement</InsertColumnListParenthesisLayout>

    <InsertEmptyLineBetweenJoins>false</InsertEmptyLineBetweenJoins>

    <InsertValuesListParenthesisLayout>CompactToStatement</InsertValuesListParenthesisLayout>

    <IsMigratedStyle>false</IsMigratedStyle>

    <JoinBreakType>ChopAlways</JoinBreakType>

    <JoinClauseAlignment>Tabbed</JoinClauseAlignment>

    <JoinConditionBreakType>Never</JoinConditionBreakType>

    <JoinOnAlignment>TabbedFromJoin</JoinOnAlignment>

    <JoinOnConditionAlignment>Tabbed</JoinOnConditionAlignment>

    <Name>John</Name>

    <NewLineAfterTopRowFilter>true</NewLineAfterTopRowFilter>

    <NewLineBeforeTopRowFilter>false</NewLineBeforeTopRowFilter>

    <NewLineForCorrelatedTableSource>true</NewLineForCorrelatedTableSource>

    <NumberOfEmptyLinesAfterBatchSeparator>1</NumberOfEmptyLinesAfterBatchSeparator>

    <NumberOfEmptyLinesBetweenStatements>1</NumberOfEmptyLinesBetweenStatements>

    <NumberOfSpacesInTab>4</NumberOfSpacesInTab>

    <OpeningParenthesisAlignment>ToStatement</OpeningParenthesisAlignment>

    <OpeningParenthesisBreakType>ChopAlways</OpeningParenthesisBreakType>

    <OptionsVersion>12</OptionsVersion>

    <OverrideParenthesesOptionsForCreateAlterStatements>true</OverrideParenthesesOptionsForCreateAlterStatements>

    <ParenthesisLayout>CompactIndented</ParenthesisLayout>

    <ParenthesisedExpressionAlignment>TabbedFromOpeningBracket</ParenthesisedExpressionAlignment>

    <ParenthesisedExpressionBreakType>ChopAlways</ParenthesisedExpressionBreakType>

    <PlaceBeginOnNewLine>true</PlaceBeginOnNewLine>

    <PlaceCaseElseOnNewLine>true</PlaceCaseElseOnNewLine>

    <PlaceCaseEndOnNewLine>true</PlaceCaseEndOnNewLine>

    <PlaceCaseExpressionOnNewLine>true</PlaceCaseExpressionOnNewLine>

    <PlaceCaseFirstWhenOnNewLine>Always</PlaceCaseFirstWhenOnNewLine>

    <PlaceClosingParenthesisOnNewLine>true</PlaceClosingParenthesisOnNewLine>

    <PlaceCommasBeforeListItems>false</PlaceCommasBeforeListItems>

    <PlaceJoinOnKeywordOnANewLine>true</PlaceJoinOnKeywordOnANewLine>

    <PlaceJoinTableOnNewLine>false</PlaceJoinTableOnNewLine>

    <PreferBreakBeforeAsKeyword>true</PreferBreakBeforeAsKeyword>

    <PreferBreakBeforeBetweenAndKeyword>false</PreferBreakBeforeBetweenAndKeyword>

    <PreferBreakBeforeBetweenKeyword>true</PreferBreakBeforeBetweenKeyword>

    <PreferBreakBeforeConditionOperator>true</PreferBreakBeforeConditionOperator>

    <PreferBreakBeforeCteName>false</PreferBreakBeforeCteName>

    <PreferBreakBeforeEquals>true</PreferBreakBeforeEquals>

    <PreferBreakBeforeInsertTable>false</PreferBreakBeforeInsertTable>

    <PreferBreakBeforeThenKeyword>false</PreferBreakBeforeThenKeyword>

    <PreserveLineSpacingBetweenStatements>true</PreserveLineSpacingBetweenStatements>

    <PreserveLineSpacingWithinStatements>false</PreserveLineSpacingWithinStatements>

    <ReservedKeywordsCasing>Uppercase</ReservedKeywordsCasing>

    <SemicolonWhitespace>None</SemicolonWhitespace>

    <SpaceBeforeUnits>false</SpaceBeforeUnits>

    <SpaceBetweenDataTypeAndParameters>false</SpaceBetweenDataTypeAndParameters>

    <SpaceInsideEmptyParentheses>false</SpaceInsideEmptyParentheses>

    <SpacesAroundArithmeticOperators>

      <m_After>true</m_After>

      <m_Before>true</m_Before>

    </SpacesAroundArithmeticOperators>

    <SpacesAroundCommas>

      <m_After>true</m_After>

      <m_Before>false</m_Before>

    </SpacesAroundCommas>

    <SpacesAroundComparisonOperators>

      <m_After>true</m_After>

      <m_Before>true</m_Before>

    </SpacesAroundComparisonOperators>

    <SpacesAroundFunctionCallArguments>

      <m_After>false</m_After>

      <m_Before>false</m_Before>

    </SpacesAroundFunctionCallArguments>

    <SpacesAroundFunctionCalls>

      <m_After>true</m_After>

      <m_Before>false</m_Before>

    </SpacesAroundFunctionCalls>

    <SpacesAroundInPredicateContents>

      <m_After>true</m_After>

      <m_Before>true</m_Before>

    </SpacesAroundInPredicateContents>

    <SpacesAroundParentheses>

      <m_After>false</m_After>

      <m_Before>false</m_Before>

    </SpacesAroundParentheses>

    <SpacesAroundParenthesesContents>

      <m_After>false</m_After>

      <m_Before>false</m_Before>

    </SpacesAroundParenthesesContents>

    <SpacesOrTabs>OnlySpaces</SpacesOrTabs>

    <SubsequentInsertColumnsListItemsBreakType>ChopAlways</SubsequentInsertColumnsListItemsBreakType>

    <SubsequentInsertValuesListItemsBreakType>SimpleWrap</SubsequentInsertValuesListItemsBreakType>

    <SubsequentListItemsBreakType>ChopAlways</SubsequentListItemsBreakType>

    <UseGlobalListOptionsForDmlStatements>false</UseGlobalListOptionsForDmlStatements>

    <UseObjectDefinitionCase>true</UseObjectDefinitionCase>

    <VerticallyAlignAliases>false</VerticallyAlignAliases>

    <VerticallyAlignClauseItems>false</VerticallyAlignClauseItems>

    <VerticallyAlignColumnDefinitions>false</VerticallyAlignColumnDefinitions>

    <VerticallyAlignComparisonOperators>false</VerticallyAlignComparisonOperators>

    <VerticallyAlignDataTypes>false</VerticallyAlignDataTypes>

    <VerticallyAlignJoinConditionWithJoinTable>false</VerticallyAlignJoinConditionWithJoinTable>

    <VerticallyAlignListItemComments>true</VerticallyAlignListItemComments>

    <VerticallyAlignListItems>true</VerticallyAlignListItems>

    <WhereClauseFirstItemBreakType>Never</WhereClauseFirstItemBreakType>

    <WrapColumn>120</WrapColumn>

    <WrapLongLines>true</WrapLongLines>

  </LayoutOptions>

  0 · Share on Twitter
• Options

  Tianjiao_Li Redgate › Posts: 684 Rose Gold 5

  October 31, 2019 12:34PM

  With your style, it still works for me oddly.
  
  Could you export your Prompt options (SQL Prompt ->Options->Export) and attach to your reply?
  
  Thanks.

  Kind regards
  
  Tianjiao Li | Redgate Software
  Have you visited our Help Center?
  

  0 · Share on Twitter
• Options

  RTSJohn Posts: 7 Bronze 1

  November 12, 2019 4:45PM

  After updating to SQL Prompt 10, I'm no longer receiving the error.

  0 · Share on Twitter