CODEWITHSUNDEEP
javascriptsql
tom
tom

Reputation: 11

SQL OR statement for multiple column names

I have a SQL query I am writing in JS. I am trying to update a timestamp to equal a different column that is already in a different table if null. Basically, if there is no value in new timestamp column, I want to use the other column but they are named something different in each table. There are Is this possible or do I have to do three separate queries? test_date line with OR statements is what I think is wrong.

if (isNil(timestamp_name)) {
  await database.query(`
  UPDATE TABLE ${tableName} 
  SET ${timestamp_name} = 
  test_date OR inserted_date OR last_run
  `)
}

Upvotes: 0

Views: 60

Answers (1)

Patrick Hurst
Patrick Hurst

Reputation: 2853

With SQL you always want to operate on sets where possible. This looks like you're looping over a result set and operating on a single row at a time. That's an anti-pattern that will cause you trouble at scale.

It may make more sense to instead operate on a set of rows. How you define that set doesn't really matter.

Here I've mocked up a table (in a variable) with some data. From what you posted it seems like you want to update the test_date column where it's currently null.

DECLARE @table1 TABLE (ID INT IDENTITY, test_date DATETIME, inserted_date DATETIME, last_run DATETIME)

INSERT INTO @table1 (test_date, inserted_date, last_run) VALUES
(NULL, NULL, '1900-01-01'),
(NULL, '1950-01-01', '1900-01-01'),
(NULL, '2000-01-01', NULL),
(GETUTCDATE(), NULL, NULL)

SELECT *
  FROM @table1

ID      test_date               inserted_date           last_run
-------------------------------------------------------------------------------
1       NULL                    NULL                    1900-01-01 00:00:00.000
2       NULL                    1950-01-01 00:00:00.000 1900-01-01 00:00:00.000
3       NULL                    2000-01-01 00:00:00.000 NULL
4       2022-10-28 20:25:39.620 NULL                    NULL

In the mock up three of the rows have a NULL test_date. We can update them using the values from the other columns like so:

UPDATE @table1
   SET test_date = COALESCE(inserted_date, last_run)
 WHERE test_date IS NULL

This will only update rows where test_date is NULL, and will set it to the value of inserted_date if that column is not NULL. If inserted_date is NULL it will set test_date to the value of last_run. You can think of COALESCE as find the first not null value in the list, and return that.

Because this operates on the SET of rows from the table where test_date is NULL you only would need to execute it once to affect ALL the rows that meet that condition.

SELECT *
  FROM @table1

ID      test_date               inserted_date           last_run
-------------------------------------------------------------------------------
1       1900-01-01 00:00:00.000 NULL                    1900-01-01 00:00:00.000
2       1950-01-01 00:00:00.000 1950-01-01 00:00:00.000 1900-01-01 00:00:00.000
3       2000-01-01 00:00:00.000 2000-01-01 00:00:00.000 NULL
4       2022-10-28 20:25:39.620 NULL                    NULL

In row 1 inserted_date was also NULL, so we used the value from last_run. In row 2 neither of the two other columns was NULL, so COALESCE returned the first in the list (inserted_date) In row 3 inserted_date was used as it was not NULL. Row 4 was not affected because it did not have a NULL for test_date.

Upvotes: 1

Related Questions