Fill two columns in SQL table as a result of an operation done on another (third) column

bluestar2022 0
Viewed 27

I have a time shift column in my tblCalendar table called TimeShift and need to fill two other columns in the same table, both columns have Null values and their names are StartTime and EndTime.

This serves as an example:

TimeShift StartTime EndTime
8:00AM - 4:00PM Null Null
10:00AM - 6:00PM Null Null
11:00AM - 7:00PM Null Null

I need to fill the StartTime and EndTime columns by doing a split on the first column.

I figured out how to use SQL functions to split the first column on '-' char.

But I didn't find a way to fill the Null columns with these extracted split data.

The query I found is:

SELECT
    LEFT(TimeShift, CHARINDEX('-', TimeShift) - 2) AS StartTime, 
    RIGHT(TimeShift, LEN(TimeShift) - CHARINDEX('-', TimeShift) -1) AS EndTime 
FROM
    tblCalendar

which will return the required data, but my question is: how to fill these values into the StartTime and EndTime columns in my original table?

Many thanks

fill calculated-columns split sql-server sql
1 Answers

Well, obviously, you'll need an UPDATE statement to update an existing table - and you can basically use the two expressions from your SELECT query.

Try something like this:

UPDATE dbo.tblCalendar
SET StartTime = LEFT(TimeShift, CHARINDEX('-', TimeShift) - 2), 
    EndTime = RIGHT(TimeShift, LEN(TimeShift) - CHARINDEX('-', TimeShift) -1) 
WHERE
    StartTime IS NULL AND EndTime IS NULL;

Depending on the datatypes of your columns, this might not work right away - maybe you also need to do some conversions from e.g. VARCHAR to TIME ....

marc_s0
Related