New training course: Customizing SQL Prompt. Watch now.

Formatting an UPDATE query with multiple items in SET phrase

RTSJohnRTSJohn Posts: 6 New member
I'm getting the error below when trying to format a properly-formed UPDATE query that uses a join and then updates more than one column in the SET phrase.  This happens even though the query parses and runs without error, and happens on multiple queries similar to this involving all different kinds of tables and joins.

I'm in SSMS 18.3.1 and SQL Prompt 9.5.22.12050.  It did this on the previous release of SQL Prompt as well.

Query looks like this:

UPDATE P SET
CompanyID = Rq.newRiskID,
ProductID = rq.newProductID
FROM #RiskQuotes AS rq
INNER JOIN dbo.Policy P ON Rq.QuoteID = P.QuoteID
WHERE Rq.newProductID IS NOT NULL


Answers

  • RTSJohnRTSJohn Posts: 6 New member
    Addendum: this error seems to be happening a lot, not just on update queries.  Every time, it's "Sequence contains more than one element."
  • 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?
  • RTSJohnRTSJohn Posts: 6 New member
    Here's the file I'm using.

    <?xml version="1.0" encoding="utf-8"?>
      <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>
  • 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?
  • RTSJohnRTSJohn Posts: 6 New member
    After updating to SQL Prompt 10, I'm no longer receiving the error.
Sign In or Register to comment.