What are the challenges you face when working across database platforms? Take the survey
Options

Formatting an UPDATE query with multiple items in SET phrase

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

  • Options
    RTSJohnRTSJohn Posts: 7 Bronze 1
    Addendum: this error seems to be happening a lot, not just on update queries.  Every time, it's "Sequence contains more than one element."
  • Options
    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?
  • Options
    RTSJohnRTSJohn Posts: 7 Bronze 1
    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>
  • Options
    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?
  • Options
    RTSJohnRTSJohn Posts: 7 Bronze 1
    After updating to SQL Prompt 10, I'm no longer receiving the error.
Sign In or Register to comment.