NULL is a special value in SQL. So to null a property, do this:

UPDATE table SET column = NULL;

hi all,

I’ve been googling for hours, and can’t find what I’m looking for. I need to update about 100+ records on a SQL DB i have, to remove information within certain fields and replace it with a null value. I do NOT want to delete any records or rows. so far I can get my query to work with 1 field at a time, but is there a way to code it to change all the fields I want to at once within a record?

Test_Code.txt (453 Bytes)

What would the syntax be to write an update statement to update the null values for fields power1, power2, power3 with the values already listed in the table?

I take this to mean that each company's nulls should be populated based on the values taken from the one populated entry that, as you say elsewhere in your post, each company has. So, in pseudo-code, your UPDATE statement would need to look something like this:

UPDATE
  #Rentarious
SET
  power1 = power1 from the same company’s populated row,
  power2 = power2 from the same company’s populated row,
  power3 = power3 from the same company’s populated row
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

One way to implement the above pattern could be to use correlated subqueries:

UPDATE
  #Rentarious
SET
  power1 = (SELECT power1 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power1 IS NOT NULL),
  power2 = (SELECT power2 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power2 IS NOT NULL),
  power3 = (SELECT power3 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power3 IS NOT NULL)
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

While that would work, such an update might not be very efficient, because the same table would be touched three extra times to obtain the source value. Since you know that the non-null values are stored in one row for each company, you could obtain them all in just one extra pass over the table by using a derived table and the proprietary "update with a join" syntax:

UPDATE
  #Rentarious
SET
  power1 = sub.power1,
  power2 = sub.power2,
  power3 = sub.power3
FROM
  (
    SELECT
      c1,
      power1,
      power2,
      power3
    FROM
      #Rentarious
    WHERE power1 IS NOT NULL
      AND power2 IS NOT NULL
      AND power3 IS NOT NULL
  ) AS sub
WHERE #Rentarious.c1 = sub.c1
  AND #Rentarious.power1 IS NULL
  AND #Rentarious.power2 IS NULL
  AND #Rentarious.power3 IS NULL
;

You can also rewrite it to use the explicit JOIN syntax:

UPDATE
  tgt
SET
  power1 = src.power1,
  power2 = src.power2,
  power3 = src.power3
FROM
  #Rentarious AS tgt
  INNER JOIN
  (
    SELECT
      c1,
      power1,
      power2,
      power3
    FROM
      #Rentarious
    WHERE power1 IS NOT NULL
      AND power2 IS NOT NULL
      AND power3 IS NOT NULL
  ) AS sub
  ON tgt.c1 = sub.c1
WHERE tgt.power1 IS NULL
  AND tgt.power2 IS NULL
  AND tgt.power3 IS NULL
;

As you can see, there is only one subquery in both variations and it provides all three values to populate the other rows.

Note, though, that it is also possible to solve this problem without any extra scans. First of all, if this were a SELECT statement, you could return the populated row's values in every row by using a window aggregate function, like this:

SELECT
  c1,
  yryryryr,
  power1,
  power2,
  power3,
  populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
  populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
  populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
FROM
  #Rentarious
;

The MAX function works in this situation because it returns the maximum value only across the non-null values in the specified set. In your case, there would be only one non-null value per partition of c1 in each of the three cases, so the function would return that one value. This would be the result of the query for the example in your question:

c1               yryryryr    power1  power2  power3  populatedPower1  populatedPower2  populatedPower3
---------------  ----------  ------  ------  ------  ---------------  ---------------  ---------------
Building Blocks  2012-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2013-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2014-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2016-01-01  Red     Blue    Green   Red              Blue             Green
Red Cement       2012-01-01  Pink    Purple  Orange  Pink             Purple           Orange
Red Cement       2016-01-01  NULL    NULL    NULL    Pink             Purple           Orange
Red Cement       2011-01-01  NULL    NULL    NULL    Pink             Purple           Orange

So the only thing remaining would be just to

SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3

And it is indeed possible to do so in SQL Server, because the result of the above SELECT query can be used as the target of your UPDATE statement. You can use it as a derived table:

UPDATE
  tgt
SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3
FROM
  (
    SELECT
      c1,
      yryryryr,
      power1,
      power2,
      power3,
      populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
      populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
      populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
    FROM
      #Rentarious
  ) AS tgt
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

or implement it as a CTE (Common Table Expression) and use the CTE's alias as the target:

WITH tgt AS
  (
    SELECT
      c1,
      yryryryr,
      power1,
      power2,
      power3,
      populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
      populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
      populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
    FROM
      #Rentarious
  )
UPDATE
  tgt
SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

Both would work equally well and result in all nulls replaced with corresponding values, i.e. from this:

c1               yryryryr    power1  power2  power3
---------------  ----------  ------  ------  ------
Building Blocks  2016-01-01  Red     Blue    Green
Red Cement       2012-01-01  Pink    Purple  Orange
Building Blocks  2012-01-01  NULL    NULL    NULL
Building Blocks  2013-01-01  NULL    NULL    NULL
Building Blocks  2014-01-01  NULL    NULL    NULL
Red Cement       2016-01-01  NULL    NULL    NULL
Red Cement       2011-01-01  NULL    NULL    NULL

to this:

c1               yryryryr    power1  power2  power3
---------------  ----------  ------  ------  ------
Building Blocks  2016-01-01  Red     Blue    Green
Red Cement       2012-01-01  Pink    Purple  Orange
Building Blocks  2012-01-01  Red     Blue    Green
Building Blocks  2013-01-01  Red     Blue    Green
Building Blocks  2014-01-01  Red     Blue    Green
Red Cement       2016-01-01  Pink    Purple  Orange
Red Cement       2011-01-01  Pink    Purple  Orange

If you've opened a table and you want to clear an existing value to NULL, click on the value, and press Ctrl+0.

You cannot compare NULLs with =.

Use this:

update userloginstats set logouttime= sysdate where logouttime is null;

This basic request will do it:

UPDATE <Table> SET <Column to update> = 0 WHERE <Column to update> IS NULL;

NB: Capital letters are not relevant.