CODEWITHSUNDEEP
sqlsql-servert-sql
JNR_Prog123
JNR_Prog123

Reputation: 133

Merge two sql queries into one

I've been working on a system service for a client for a while and need some help, I need to merge two sql queries into one. The first part of the query is to look at the master sequence number and count it, after which the query must update a field. I have the two queries below if anyone can help with this problem.

Count query

 SELECT master_seq, count(master_seq) as NofH
 FROM [ZS_CS_EVO_Integration].[dbo].[CS_Consolidation]
 where delivery_date = '2016-07-01'
 GROUP BY master_seq
 order by master_seq

Update Query

 (" UPDATE [dbo].[CS_Consolidation]"
+ " SET [split_dlv] = 1"
+ " FROM [dbo].[CS_Consolidation]"
+ " WHERE"
+ " [master_seq] <> 0 AND CONVERT(DATE,delivery_date) = '" + yesterday + "'", IntConnect);

Upvotes: 1

Views: 67

Answers (1)

gofr1
gofr1

Reputation: 15997

You can put the first part into CTE, then join and UPDATE:

DECLARE @delivery_date DATE = '2016-07-01'

;WITH cte AS (
    SELECT master_seq
    FROM [ZS_CS_EVO_Integration].[dbo].[CS_Consolidation]
    where delivery_date = @delivery_date and [master_seq] <> 0 
    GROUP BY master_seq
    HAVING count(master_seq) > 1
)

UPDATE c
SET [split_dlv] = 1
FROM [dbo].[CS_Consolidation] c
INNER JOIN cte t
    ON t.master_seq = c.master_seq and c.delivery_date = @delivery_date

Upvotes: 1

Related Questions