none
SQL Update a Single Row Multiple Times Using 2 Data Sets

    Question

  • I'm working in tsql and have an issue where I need to do multiple updates to a single row based on multiple conditions. 

    1. By Rank_
    2. If the column is NULL I need it to update no matter what the Rank is.
    3. If the Ranks are the same I need it to update in order of T2_ID.
    4. And I need it to use the last updated output.

    I've tried using the update statement below but it only does the first update and the rest are ignored. Here is an example of the data sets i'm working w/ and the Desired results. Thanks in advance!






    update a
    set Middle = case when a.Rank_> b.Rank_ OR a.Middle IS NULL then ISNULL(b.Middle,a.Middle) end,
    LName = case when a.Rank_> b.Rank_ OR a.Lname IS NULL then ISNULL(b.LName,a.LName) end,
    Rank_ = case when a.Rank_> b.Rank_ then b.Rank_ end
    from #temp1 a
    inner join #temp2 b on a.fname = b.fname
    where b.T2_ID in (select top 100% T2_ID from #temp2 order by T2_ID asc)


    • Edited by pmyahoo Tuesday, September 02, 2014 4:24 AM img size
    Tuesday, September 02, 2014 4:23 AM

Answers

  • Merge #temp1 a
    Using #temp2 b on a.fname = b.fname
    When Matched Then
    Update 
    SET
     Middle = case when a.Rank_> b.Rank_ OR a.Middle IS NULL then ISNULL(b.Middle,a.Middle) end,
    LName = case when a.Rank_> b.Rank_ OR a.Lname IS NULL then ISNULL(b.LName,a.LName) end,
    Rank_ = case when a.Rank_> b.Rank_ then b.Rank_ end;

    Tuesday, September 02, 2014 12:53 PM
  • The Merge clause actually errors because it attempt to update the same record.  I think this CTE statement is the closest I've come but I'm still working through it as I'm not too familiar w/ them.  It returns multiple rows which I will have to insert into a temp table to update since the resulting row I need is the last in the table.

    ;WITH cteRowNumber 
    AS(
    	Select DISTINCT
    		Row_Number() OVER(PARTITION BY a.LName ORDER BY a.LName ASC, a.Rank_ DESC,b.T2ID ASC) AS RowNumber
    			,a.FName
    			,a.LName
    			,b.LName as xLname
    			,a.MName
    			,b.MName AS xMName
    			,a.Rank_
    			,b.Rank_ AS xRank
    			,b.T2ID
    		FROM #temp1 a
    		inner join #temp2 b
    			ON a.fname = b.fname
    	), cteCursor
    		AS(
    			Select a.RowNumber,
    				a.Fname
    				,a.LName
    				,a.xLname
    				,a.MName
    				,a.xMName
    				,a.xRank
    				,a.T2ID
    				,CASE WHEN a.Rank_ >= a.xRank THEN ISNULL(a.xRank,a.Rank_) else ISNULL(a.Rank_,a.xRank) end AS Alt_Rank_
    				,CASE WHEN a.Rank_ >= a.xRank THEN ISNULL(a.xMName,a.MName) else ISNULL(a.MName,a.xMName) end AS Alt_MName
    				,CASE WHEN a.Rank_ >= a.xRank THEN ISNULL(a.xLName,a.lname) else ISNULL(a.LName,a.xlname) end as Alt_Lname
    			FROM cteRowNumber a
    			where a.RowNumber = 1
    			UNION ALL
    			Select crt.RowNumber
    				,crt.FName
    				,crt.LName
    				,crt.xLname
    				,crt.MName
    				,crt.xMName
    				,crt.xRank
    				,crt.T2ID
    				,CASE WHEN Prev.Alt_Rank_ >= crt.xRank THEN ISNULL(crt.xRank,Prev.Alt_Rank_) else ISNULL(Prev.Alt_Rank_,crt.xRank) end AS Alt_Rank
    				,CASE WHEN Prev.Alt_Rank_ >= crt.xRank THEN ISNULL(crt.xMName,Prev.Alt_MName) else ISNULL(Prev.Alt_MName,crt.xMName) end AS Alt_MName
    				,CASE WHEN Prev.Alt_Rank_ >= crt.xRank THEN ISNULL(crt.xLName,Prev.Alt_Lname) else ISNULL(Prev.Alt_Lname,crt.xLName) end as Alt_Lname
    			FROM cteCursor prev
    			inner join cteRowNumber crt
    				on prev.fname = crt.fname and prev.RowNumber + 1 = crt.RowNumber
    			)
    SELECT   cte.*
    FROM    cteCursor cte
    

    Wednesday, September 03, 2014 1:32 AM

All replies

  • I have doubts regarding this statement , does it throw an error?

    (select top 100% T2_ID from #temp2 order by T2_ID asc)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, September 02, 2014 9:40 AM
  • Should be:

    select top 100 percent T2_ID from #temp2 order by T2_ID asc

    Tuesday, September 02, 2014 9:50 AM
  • Merge #temp1 a
    Using #temp2 b on a.fname = b.fname
    When Matched Then
    Update 
    SET
     Middle = case when a.Rank_> b.Rank_ OR a.Middle IS NULL then ISNULL(b.Middle,a.Middle) end,
    LName = case when a.Rank_> b.Rank_ OR a.Lname IS NULL then ISNULL(b.LName,a.LName) end,
    Rank_ = case when a.Rank_> b.Rank_ then b.Rank_ end;

    Tuesday, September 02, 2014 12:53 PM
  • The Merge clause actually errors because it attempt to update the same record.  I think this CTE statement is the closest I've come but I'm still working through it as I'm not too familiar w/ them.  It returns multiple rows which I will have to insert into a temp table to update since the resulting row I need is the last in the table.

    ;WITH cteRowNumber 
    AS(
    	Select DISTINCT
    		Row_Number() OVER(PARTITION BY a.LName ORDER BY a.LName ASC, a.Rank_ DESC,b.T2ID ASC) AS RowNumber
    			,a.FName
    			,a.LName
    			,b.LName as xLname
    			,a.MName
    			,b.MName AS xMName
    			,a.Rank_
    			,b.Rank_ AS xRank
    			,b.T2ID
    		FROM #temp1 a
    		inner join #temp2 b
    			ON a.fname = b.fname
    	), cteCursor
    		AS(
    			Select a.RowNumber,
    				a.Fname
    				,a.LName
    				,a.xLname
    				,a.MName
    				,a.xMName
    				,a.xRank
    				,a.T2ID
    				,CASE WHEN a.Rank_ >= a.xRank THEN ISNULL(a.xRank,a.Rank_) else ISNULL(a.Rank_,a.xRank) end AS Alt_Rank_
    				,CASE WHEN a.Rank_ >= a.xRank THEN ISNULL(a.xMName,a.MName) else ISNULL(a.MName,a.xMName) end AS Alt_MName
    				,CASE WHEN a.Rank_ >= a.xRank THEN ISNULL(a.xLName,a.lname) else ISNULL(a.LName,a.xlname) end as Alt_Lname
    			FROM cteRowNumber a
    			where a.RowNumber = 1
    			UNION ALL
    			Select crt.RowNumber
    				,crt.FName
    				,crt.LName
    				,crt.xLname
    				,crt.MName
    				,crt.xMName
    				,crt.xRank
    				,crt.T2ID
    				,CASE WHEN Prev.Alt_Rank_ >= crt.xRank THEN ISNULL(crt.xRank,Prev.Alt_Rank_) else ISNULL(Prev.Alt_Rank_,crt.xRank) end AS Alt_Rank
    				,CASE WHEN Prev.Alt_Rank_ >= crt.xRank THEN ISNULL(crt.xMName,Prev.Alt_MName) else ISNULL(Prev.Alt_MName,crt.xMName) end AS Alt_MName
    				,CASE WHEN Prev.Alt_Rank_ >= crt.xRank THEN ISNULL(crt.xLName,Prev.Alt_Lname) else ISNULL(Prev.Alt_Lname,crt.xLName) end as Alt_Lname
    			FROM cteCursor prev
    			inner join cteRowNumber crt
    				on prev.fname = crt.fname and prev.RowNumber + 1 = crt.RowNumber
    			)
    SELECT   cte.*
    FROM    cteCursor cte
    

    Wednesday, September 03, 2014 1:32 AM