SQL Server Performance

Changing NULL without ISNULL etc

Discussion in 'ALL SQL SERVER QUESTIONS' started by Blueviper, Feb 3, 2013.

  1. Blueviper New Member

    Hello,

    I've been set a 'challenge' query. Basically if a certain column has NULL values, I need to replace those NULL values with another value (obtained from a subquery). So I thought, hey that's easy, I'll use ISNULL/COALESCE however I've been told I can't and instead I can only use JOINS/UNIONS and IS NULL.

    Unfortunately I don't know where to start. I was wondering if you could give me some pointers as to how it would be done generally using JOINS/UNIONS and IS NULL.

    Thanks in advance.
  2. davidfarr Member

    You are somewhat vague regarding where to source the "another value (obtained from a subquery)" and this detail is an imporant part of creating the correct syntax to get it.

    Based on the brief explanation that you have given, I can suggest you start with either;

    UPDATE
    FirstTable SET Column01 =--(subquery)
    WHERE
    Column01 IS NULL
    

    Or you can try...

    UPDATE
    FirstTable SET FirstTable.Column01 = SecondTable.ColumnNewValue FROM FirstTable, SecondTable
    WHERE
    FirstTable.UniqueKeyField = SecondTable.UniqueKeyField AND FirstTable.Column01 IS NULL
  3. davidfarr Member

    Just as addition;
    Your initial post did not specify that your intended query was a DAL (select) type, which is why I proposed an UPDATE query.
    To achieve this as a SELECT, I would suggest something like;

    SELECT Column01 from FirstTable WHERE Column01 IS NOT NULL
    UNION ALL
    SELECT SecondTable.ColumnNewValue FROM SecondTable
    INNER JOIN FirstTable ON FirstTable.UniqueKeyField = SecondTable.UniqueKeyField 
    WHERE FirstTable.Column01 IS NULL
  4. Kash69 New Member

    Sometimes this works also:

    Update Table
    Set Column1=New Value
    Where Column1=Null

    I think you need to have a setting enabled on SQL Server tho

Share This Page