SQL – Update multiple rows with a single key for multiple times

January 17, 2024

I have 366 date IDs for the year 2024, and with these IDs I need to prepare a set of data that I need to upload into a table.

This is the query for the date IDs.

select date_id
from hire_date
where calendar_year = 2024
order by date_id

the date IDs are:

5489
5490
5491
....

I need to use the below data to update the date_id column with the 2024 data IDs.

date_id region_no_in    ops_hired_hours
5488        2            1368
5488        3            1464
5488        4            1368
5488        8            936
5488        9            936
5488        11           72
5488        13           192
5488        19           48
5488        22           72

The data above is from this query:

SELECT [date_id], [region_no_in] ,[ops_hired_hours]
FROM hire_hour_dim]
where date_id in (5488)

The final result needs to be like this.

date_id region_no_in    ops_hired_hours
5489        2            1368
5489        3            1464
5489        4            1368
5489        8            936
5489        9            936
5489        11           72
5489        13           192
5489        19           48
5489        22           72
5490        2            1368
5490        3            1464
5490        4            1368
5490        8            936
5490        9            936
5490        11           72
5490        13           192
5490        19           48
5490        22           72
5491        2            1368
5491        3            1464
5491        4            1368
5491        8             936
......

And so on.

Thanks for the help.

>Solution :

You could combine these two datasets using a cross join.

Try the following command first and ensure it’s working as you wish before any insertion or updates

SELECT new_dates.date_id, old_data.region_no_in, old_data.ops_hired_hours
FROM 
  (SELECT date_id FROM hire_date WHERE calendar_year = 2024) AS new_dates
CROSS JOIN 
  (SELECT region_no_in, ops_hired_hours FROM hire_hour_dim WHERE date_id = 5488) AS old_data;

The new data could be inserted into a new table, using

INSERT INTO your_target_table (date_id, region_no_in, ops_hired_hours)
SELECT new_dates.date_id, old_data.region_no_in, old_data.ops_hired_hours
FROM 
  (SELECT date_id FROM hire_date WHERE calendar_year = 2024) AS new_dates
CROSS JOIN 
  (SELECT region_no_in, ops_hired_hours FROM hire_hour_dim WHERE date_id = 5488) AS old_data;

Or we could update the current table with something like

UPDATE your_target_table
SET ops_hired_hours = (
    SELECT old_data.ops_hired_hours
    FROM hire_hour_dim AS old_data
    WHERE old_data.region_no_in = your_target_table.region_no_in
      AND old_data.date_id = 5488
)
WHERE date_id IN (
    SELECT date_id
    FROM hire_date
    WHERE calendar_year = 2024
);