Formatting an UPDATE query with multiple items in SET phrase

RTSJohn

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

RTSJohn

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

Tianjiao_Li

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.

RTSJohn

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>

Tianjiao_Li

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.

RTSJohn

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