KyloRen KyloRen - 4 months ago 6
SQL Question

Combine two SQL queries?

I have two tables,

Table_1
and
Table_2
.

I am using this SQL statement to count how many times a particular piece of data occurs in column
Info_Data
in
Table_1
. In its current hard coded form it returns a value of
9


SELECT Staff_No, Info_Data, COUNT(*) cCount
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data_1'
GROUP BY Staff_No, Info_Data


I then have another SQL statement that checks to see if a row exists in
Table_2
, if it does not, insert a row and update the data. If it does ,just update the data.

IF EXISTS (SELECT * FROM Staff_Manager.dbo.Staff_Count_TBL WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = 1)
BEGIN
UPDATE Staff_Manager.dbo.Staff_Count_TBL
SET Column_Value = 9
WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = 1
END
ELSE
BEGIN
INSERT INTO Staff_Manager.dbo.Staff_Count_TBL (Staff_No, Year_D, Month_D, Column_Index, Column_Value)
VALUES (3201, 2016, 6, 1, 9)
END


Both these statements are working how they are supposed to.

But I can't find a way to combine the two statements, I tried
JOIN
,
MERGE
with no luck.
Being that the value that the first statement returns is
9
, I want to replace the hard coded
9
on this line
SET Column_Value = 9
and the
Column_Value
columns value
9
on this line
VALUES (3201, 2016, 6, 1, 9)
with result of the first statement

This obviously is incorrect, but to illustrate what I am sort of trying to do.

IF EXISTS (SELECT * FROM Staff_Manager.dbo.Staff_Count_TBL WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = 1)
BEGIN
UPDATE Staff_Manager.dbo.Staff_Count_TBL
SET Column_Value = SELECT Staff_No, Info_Data, COUNT(*) cCount
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data_1'
GROUP BY Staff_No, Info_Data
WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = 1
END
ELSE
BEGIN
INSERT INTO Staff_Manager.dbo.Staff_Count_TBL (Staff_No, Year_D, Month_D, Column_Index, Column_Value)
VALUES (3201, 2016, 6, 1, SELECT Staff_No, Info_Data, COUNT(*) cCount
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data_1'
GROUP BY Staff_No, Info_Data)
END

Answer

Your first query returns more than one column and you can't use it to update or insert a single column. Instead, make it to return only count() and try

UPDATE Staff_Manager.dbo.Staff_Count_TBL 
SET Column_Value = (
     SELECT COUNT(*)  
     FROM Staff_Manager.dbo.Staff_Time_TBL
     WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data_1' 
     GROUP BY Staff_No, Info_Data
    )
WHERE Staff_No = 3201 AND Year_D = 2016 AND Month_D = 6 AND Column_Index = 1

and

INSERT INTO Staff_Manager.dbo.Staff_Count_TBL 
(Staff_No, Year_D, Month_D, Column_Index, Column_Value)
SELECT 3201, 2016, 6, 1, COUNT(*) 
     FROM Staff_Manager.dbo.Staff_Time_TBL
     WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/1' AND '2016/7/1' AND Info_Data = 'Data_1' 
     GROUP BY Staff_No, Info_Data

As you can see, in INSERT there is no VALUES construction, read http://www.w3schools.com/sql/sql_insert_into_select.asp for more details. Also see how to UPDATE from SELECT using SQL Server.

P.S. Using of BEGIN/END is not required for single queries and they could be skipped.