Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: can this query be rewritten??

    update CMS_RISK_SCORES
    set MAX_MCARA_RISK_RTE = (select max(MCARA_RISK_RTE) from XTAW0200_MEM_DTL A
    where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM),
    MAX_MCARD_RISK_ADJ_RTE = (select max(MCARD_RISK_ADJ_RTE) from XTAW0200_MEM_DTL A
    where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM)

    Can I get the same results with one join instead of two without creating a temporary table?

    Thanks much.


  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    update	CMS_RISK_SCORES
    set	MAX_MCARA_RISK_RTE = MaxValues.MCARA_RISK_RTE,
    	MAX_MCARD_RISK_ADJ_RTE = MaxValues.MCARD_RISK_ADJ_RTE
    from	CMS_RISK_SCORES
    	inner join --MaxValues
    	    (select HIC_NUM,
    		    max(MCARA_RISK_RTE) as MCARA_RISK_RTE,
    		    max(MCARD_RISK_ADJ_RTE) as MCARD_RISK_ADJ_RTE
    	    from    XTAW0200_MEM_DTL
    	    group by HIC_NUM) MaxValues
    	    on CMS_RISK_SCORES.HIC_NUM = MaxValues.HIC_NUM
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2007
    Posts
    7
    Thank You.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While there is a difference in syntax, I don't think there will be any significant difference in execution plan between the two statments. SQL Server is very good at combining redundant queries like this.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •