Merge multiple tables into one row in a new table

UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);

UPDATE the_main_table
SET D_high = (
SELECT D_high
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_low = (
SELECT D_low
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_low_volume = (
SELECT D_low_volume
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_high_volume = (
SELECT D_high_volume
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_Margin = (
SELECT D_Margin
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_Volume = (
SELECT D_Volume
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);

UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_high = (
SELECT H_high
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_low = (
SELECT H_low
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_low_volume = (
SELECT H_low_volume
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_high_volume = (
SELECT H_high_volume
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_Margin = (
SELECT H_Margin
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_Volume = (
SELECT H_Volume
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);






UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET l_high = (
SELECT l_high
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET l_low = (
SELECT l_low
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET L_low_Time = (
SELECT L_low_Time
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET L_high_Time = (
SELECT L_high_Time
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET L_Margin = (
SELECT L_Margin
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);





UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_high = (
SELECT T_high
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_low = (
SELECT T_low
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_low_volume = (
SELECT T_low_volume
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_high_volume = (
SELECT T_high_volume
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_Margin = (
SELECT T_Margin
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_Volume = (
SELECT T_Volume
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);


UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_high = (
SELECT F_high
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_low = (
SELECT F_low
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_low_volume = (
SELECT F_low_volume
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_high_volume = (
SELECT F_high_volume
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_Margin = (
SELECT F_Margin
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_Volume = (
SELECT F_Volume
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);

I have this massive SQL quarry. This does preform what I’m trying to accomplish “merge multiple tables columns into new table, within 1 row”

However it is very slow with only a few thousand rows of data.

Is there a way I can shorten this quarry and still accomplish the same thing?

I have tried things like JOIN & union but have not been able to get them to function properly. Making this really long quarry has been the only solution that has actually worked.

Answer

I’m not going to pore over all your code. But it is clear from the first few examples that you can use join. For instance:

UPDATE the_main_table m JOIN
       1d_high_today ht
       ON ht.osrs_id = m.osrs_id
    SET m.item_Name = ht.item_Name
        m.D_high = ht.D_high,
        m.D_low =  ht.D_low,
        . . .;

For this, you want an index on 1d_high_today(osrs_id).