Big SQL
Largest SQL string I've ever done.
Strange; I may believe I'm the first (or only) to do what is coded below - an update statement that includes several select (and sub-select) queries within a single string statement... when I looked on the internet to help divine how to accomplish this task, the only answer I could find was "don't try it; just rebuild your tables..."
I, however, cannot rebuild a 20-year-old, half-million-record table; seems a 25-year-old company has a problem with me reorganizing every employment record they have. So I had to try the "don't do it" way; had to query a re-query on a table that re-queries itself x times and displays all long integer types as varchar (stupid table)...
And here it is:
UPDATE ttd_Trending_Roster
SET Agent_Name =
(
SELECT NT_LOGIN
FROM ttd_Test_HR
WHERE CAST(ttd_Test_HR.EMPLOYEE_NUMBER AS INT) = ttd_Trending_Roster.Employee_ID
),
Phone_Login =
(
SELECT PHONE_LOGIN
From ttd_Test_HR
WHERE CAST(ttd_Test_HR.EMPLOYEE_NUMBER AS INT) = ttd_Trending_Roster.Employee_ID
),
Job_Title =
(
SELECT JOB_NAME
FROM ttd_Test_HR
WHERE CAST(ttd_Test_HR.EMPLOYEE_NUMBER AS INT) = ttd_Trending_Roster.Employee_ID
),
Uplevel_1 =
(
SELECT CAST(SUPERVISOR_EMPLOYEE_NUMBER AS INT)
FROM ttd_Test_HR
WHERE CAST(ttd_Test_HR.EMPLOYEE_NUMBER AS INT) = ttd_Trending_Roster.Employee_ID
),
Uplevel_2 =
(
SELECT CAST(cd.SUPERVISOR_EMPLOYEE_NUMBER AS INT) AS Uplevel_2
FROM ttd_Test_HR AS ad,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS cd
WHERE ad.EMPLOYEE_NUMBER = ttd_Trending_Roster.Employee_ID
AND cd.EMPLOYEE_NUMBER = ad.SUPERVISOR_EMPLOYEE_NUMBER
),
Uplevel_3 =
(
SELECT CAST(md.SUPERVISOR_EMPLOYEE_NUMBER AS INT) As Uplevel_3
FROM ttd_Test_HR AS ad,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS cd,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS md
WHERE ad.EMPLOYEE_NUMBER = ttd_Trending_Roster.Employee_ID
AND cd.EMPLOYEE_NUMBER = ad.SUPERVISOR_EMPLOYEE_NUMBER
AND md.EMPLOYEE_NUMBER = cd.SUPERVISOR_EMPLOYEE_NUMBER
),
Uplevel_4 =
(
SELECT CAST(gd.SUPERVISOR_EMPLOYEE_NUMBER AS INT) AS Uplevel_4
FROM ttd_Test_HR AS ad,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS cd,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS md,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS gd
WHERE ad.EMPLOYEE_NUMBER = ttd_Trending_Roster.Employee_ID
AND cd.EMPLOYEE_NUMBER = ad.SUPERVISOR_EMPLOYEE_NUMBER
AND md.EMPLOYEE_NUMBER = cd.SUPERVISOR_EMPLOYEE_NUMBER
AND gd.EMPLOYEE_NUMBER = md.SUPERVISOR_EMPLOYEE_NUMBER
)
FROM ttd_Trending_Roster, ttd_Test_HR
WHERE Attritted_Effective IS NULL
Strange; I may believe I'm the first (or only) to do what is coded below - an update statement that includes several select (and sub-select) queries within a single string statement... when I looked on the internet to help divine how to accomplish this task, the only answer I could find was "don't try it; just rebuild your tables..."
I, however, cannot rebuild a 20-year-old, half-million-record table; seems a 25-year-old company has a problem with me reorganizing every employment record they have. So I had to try the "don't do it" way; had to query a re-query on a table that re-queries itself x times and displays all long integer types as varchar (stupid table)...
And here it is:
UPDATE ttd_Trending_Roster
SET Agent_Name =
(
SELECT NT_LOGIN
FROM ttd_Test_HR
WHERE CAST(ttd_Test_HR.EMPLOYEE_NUMBER AS INT) = ttd_Trending_Roster.Employee_ID
),
Phone_Login =
(
SELECT PHONE_LOGIN
From ttd_Test_HR
WHERE CAST(ttd_Test_HR.EMPLOYEE_NUMBER AS INT) = ttd_Trending_Roster.Employee_ID
),
Job_Title =
(
SELECT JOB_NAME
FROM ttd_Test_HR
WHERE CAST(ttd_Test_HR.EMPLOYEE_NUMBER AS INT) = ttd_Trending_Roster.Employee_ID
),
Uplevel_1 =
(
SELECT CAST(SUPERVISOR_EMPLOYEE_NUMBER AS INT)
FROM ttd_Test_HR
WHERE CAST(ttd_Test_HR.EMPLOYEE_NUMBER AS INT) = ttd_Trending_Roster.Employee_ID
),
Uplevel_2 =
(
SELECT CAST(cd.SUPERVISOR_EMPLOYEE_NUMBER AS INT) AS Uplevel_2
FROM ttd_Test_HR AS ad,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS cd
WHERE ad.EMPLOYEE_NUMBER = ttd_Trending_Roster.Employee_ID
AND cd.EMPLOYEE_NUMBER = ad.SUPERVISOR_EMPLOYEE_NUMBER
),
Uplevel_3 =
(
SELECT CAST(md.SUPERVISOR_EMPLOYEE_NUMBER AS INT) As Uplevel_3
FROM ttd_Test_HR AS ad,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS cd,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS md
WHERE ad.EMPLOYEE_NUMBER = ttd_Trending_Roster.Employee_ID
AND cd.EMPLOYEE_NUMBER = ad.SUPERVISOR_EMPLOYEE_NUMBER
AND md.EMPLOYEE_NUMBER = cd.SUPERVISOR_EMPLOYEE_NUMBER
),
Uplevel_4 =
(
SELECT CAST(gd.SUPERVISOR_EMPLOYEE_NUMBER AS INT) AS Uplevel_4
FROM ttd_Test_HR AS ad,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS cd,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS md,
(
SELECT EMPLOYEE_NUMBER, SUPERVISOR_EMPLOYEE_NUMBER
FROM ttd_Test_HR
) AS gd
WHERE ad.EMPLOYEE_NUMBER = ttd_Trending_Roster.Employee_ID
AND cd.EMPLOYEE_NUMBER = ad.SUPERVISOR_EMPLOYEE_NUMBER
AND md.EMPLOYEE_NUMBER = cd.SUPERVISOR_EMPLOYEE_NUMBER
AND gd.EMPLOYEE_NUMBER = md.SUPERVISOR_EMPLOYEE_NUMBER
)
FROM ttd_Trending_Roster, ttd_Test_HR
WHERE Attritted_Effective IS NULL