none
Update with Case When Then End RRS feed

  • Question

  • Something seems to be off in my SQL statement, but I'm not sure what.  Here is what I am testing.

    UPDATE Price
    SET    ISN = (case when CW.id_cd = 'ISN' THEN CW.id_number end),
           SED = (case when CW.id_cd = 'SED' THEN CW.id_number end),
           CSP = (case when CW.id_cd = 'CSP' THEN CW.id_number end)
    FROM   CW
    WHERE  Price.ID_GLOBAL = CW.asset_id

    Here is a simple screen shot of my schema.  So, now the yellow cells are missing IDs.  I'm trying to get them filled in with the values I'm showing here.

    

    I have all IDs in my CW the SQL above seems to update most of the same IDs in my Price table.  But, for some reason, some IDs in the Price table are null, but they do exist in the CW table.  I guess something is off with the SQL, but I'm not sure what.  Any thoughts?

    I am using SQL Server 2019.


    MY BOOK


    • Edited by ryguy72 Wednesday, December 11, 2019 2:48 PM
    Wednesday, December 11, 2019 1:58 PM

All replies

  • UPDATE Price
    SET    ISN = (case when CW.id_cd = 'ISN' THEN CW.id_number  else ISN end),
           SED = (case when CW.id_cd = 'SED' THEN CW.id_number else SED  end),
           CSP = (case when CW.id_cd = 'CSP' THEN CW.id_number else CSP end)
    FROM   CW join Price
    on Price.ID_GLOBAL = CW.asset_id

    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

    • Proposed as answer by Guoxiong Yuan Wednesday, December 11, 2019 3:14 PM
    Wednesday, December 11, 2019 2:04 PM
    Answerer
  • Hello Uri.  What's the logic here?  If the field in the Price table is already filled in, don't update it based on the ID in the CW table?  Is that what's happening here?  Let's say I can't trust what's in the Price table, for all IDs.  I want to override all IDs in the Price table, with all IDs in the CW table.  Will your code do this?  

    It takes a long time to re-run all my scripts to produce a specific report.  This is one of the last steps of many prior steps.


    MY BOOK



    • Edited by ryguy72 Wednesday, December 11, 2019 2:13 PM
    Wednesday, December 11, 2019 2:08 PM
  • Well, then please post sample data along with desired result, I will be able to test it.

    >>>ISN = (case when CW.id_cd = 'ISN' THEN CW.id_number  else ISN end),

    if CW.id_cd  is not ISN  keep in the original value

    >>>I want to override all IDs in the Price table, with all IDs in the CW table.  

    yep, based on JOIN condition


    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

    Wednesday, December 11, 2019 2:13 PM
    Answerer
  • If you only want to update ISN, SED and CSP which are NULL, try this:

    UPDATE p
    SET p.ISN = ISNULL(p.ISN, c.id_number), 
    	p.SED = ISNULL(p.SED, c.id_number), 
    	p.CSP = ISNULL(p.CSP, c.id_number)
    FROM Price AS p
    INNER JOIN CW AS c ON c.asset_id = p.ID_GLOBAL;


    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 11, 2019 2:47 PM
  • I just updated my original post.  I'm pretty confident that the IDs in the Price table are correct, but I'd actually like to update all IDs based on the relationship to the CW table.  I don't think updating only null records will help, because, if the ID is not correct in the Price table, the Update will not fix the potential problem.  

    MY BOOK

    Wednesday, December 11, 2019 2:55 PM
  • Your updated sample data has the multiple to multiply relationships between both  tables. 

    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 11, 2019 3:06 PM
  • I have multiple IDs in both tables.  That's correct.  

    MY BOOK

    Wednesday, December 11, 2019 3:19 PM
  • Uri's query should work. If the table CW is big, you can get the DISTINCT rows first and then update the table Price:

    DECLARE @Price TABLE (
    	ID_GLOBAL varchar(20),
    	ISN varchar(20),
    	SED varchar(20),
    	CSP varchar(20)
    );
    DECLARE @CW TABLE (
    	asset_id varchar(20),
    	id_cd varchar(20),
    	id_number varchar(20)
    );
    
    INSERT INTO @Price VALUES ('cf8a0f57', '', '', '7JQ9'), ('cf8a0f57', '', '', '7JQ9');
    INSERT INTO @CW VALUES ('cf8a0f57', 'CSP', '7JQ9'), ('cf8a0f57', 'CSP', '7JQ9'), ('cf8a0f57', 'CSP', '7JQ9');
    
    WITH CTE AS (
    	SELECT DISTINCT asset_id, id_cd, id_number
    	FROM @CW
    )
    
    UPDATE p
    SET ISN = CASE WHEN c.id_cd = 'ISN' THEN c.id_number ELSE ISN END,
    	SED = CASE WHEN c.id_cd = 'SED' THEN c.id_number ELSE SED END,
    	CSP = CASE WHEN c.id_cd = 'CSP' THEN c.id_number ELSE CSP END
    FROM @Price AS p
    INNER JOIN CTE AS c ON c.asset_id = p.ID_GLOBAL
    
    SELECT * FROM @Price;


    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 11, 2019 3:56 PM