How do I update multiple rows in SQL server

Question

0.00/5 (No votes)

See more:

Hello-

I have a table that needs to be updated with the number of orders each employee submitted depending on the region, state and other variables. But what my update is doing is counting the total of all variables and updating each with the same value. I'm not sure how to count each separately.

Here are two tables, one shows what my update produces (which is not what I want) and the other is what I'm hoping to achieve for each employee. I only updated the number for employee 1 but I'd like to get all employees updated. I apologize for the table view, it didn't copy over well

Not what I want....

business_date	day_of_week	region	state	wire	emp_01	emp_02	emp_03	emp_04	emp_05
2023-08-06	Sunday	North	Michigan	internal	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	South	Texas	internal	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	East	New York	internal	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	West	Arizona	internal	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	North	Wisconsin	internal	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	South	Florida	internal	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	East	Maine	internal	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	West	Oregon	internal	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	North	Michigan	external	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	South	Texas	external	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	East	New York	external	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	West	Arizona	external	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	North	Wisconsin	external	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	South	Florida	external	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	East	Maine	external	572	NULL	NULL	NULL	NULL
2023-08-06	Sunday	West	Oregon	external	572	NULL	NULL	NULL	NULL

What I want...

business_date	day_of_week	region	state	wire	emp_01	emp_02	emp_03	emp_04	emp_05
2023-08-06	Sunday	North	Michigan	internal	35	NULL	NULL	NULL	NULL
2023-08-06	Sunday	South	Texas	internal	32	NULL	NULL	NULL	NULL
2023-08-06	Sunday	East	New York	internal	46	NULL	NULL	NULL	NULL
2023-08-06	Sunday	West	Arizona	internal	19	NULL	NULL	NULL	NULL
2023-08-06	Sunday	North	Wisconsin	internal	37	NULL	NULL	NULL	NULL
2023-08-06	Sunday	South	Florida	internal	52	NULL	NULL	NULL	NULL
2023-08-06	Sunday	East	Maine	internal	12	NULL	NULL	NULL	NULL
2023-08-06	Sunday	West	Oregon	internal	9	NULL	NULL	NULL	NULL
2023-08-06	Sunday	North	Michigan	external	58	NULL	NULL	NULL	NULL
2023-08-06	Sunday	South	Texas	external	29	NULL	NULL	NULL	NULL
2023-08-06	Sunday	East	New York	external	23	NULL	NULL	NULL	NULL
2023-08-06	Sunday	West	Arizona	external	57	NULL	NULL	NULL	NULL
2023-08-06	Sunday	North	Wisconsin	external	49	NULL	NULL	NULL	NULL
2023-08-06	Sunday	South	Florida	external	39	NULL	NULL	NULL	NULL
2023-08-06	Sunday	East	Maine	external	53	NULL	NULL	NULL	NULL
2023-08-06	Sunday	West	Oregon	external	22	NULL	NULL	NULL	NULL

Thank you !!!!

What I have tried:

SQL

CREATE TABLE temp_table
(
    business_date DATETIME
    ,day_of_week VARCHAR(20)
    ,region VARCHAR(20)
    ,state VARCHAR(20)
    ,wire VARCHAR(20)
    ,emp_01 INT
    ,emp_02 INT
    ,emp_03 INT
    ,emp_04 INT
    ,emp_05 INT
)


INSERT INTO temp_table (business_date, day_of_week, region, state, wire)
VALUES
    ('2023-08-06', 'Sunday', 'North', 'Michigan', 'internal')
    ,('2023-08-06', 'Sunday', 'South', 'Texas', 'internal')
    ,('2023-08-06', 'Sunday', 'East', 'New York', 'internal')
    ,('2023-08-06', 'Sunday', 'West', 'Arizona', 'internal')
    
    ,('2023-08-06', 'Sunday', 'North', 'Wisconsin', 'internal')
    ,('2023-08-06', 'Sunday', 'South', 'Florida', 'internal')
    ,('2023-08-06', 'Sunday', 'East', 'Maine', 'internal')
    ,('2023-08-06', 'Sunday', 'West', 'Oregon', 'internal')
    
    ,('2023-08-06', 'Sunday', 'North', 'Michigan', 'external')
    ,('2023-08-06', 'Sunday', 'South', 'Texas', 'external')
    ,('2023-08-06', 'Sunday', 'East', 'New York', 'external')
    ,('2023-08-06', 'Sunday', 'West', 'Arizona', 'external')
    
    ,('2023-08-06', 'Sunday', 'North', 'Wisconsin', 'external')
    ,('2023-08-06', 'Sunday', 'South', 'Florida', 'external')
    ,('2023-08-06', 'Sunday', 'East', 'Maine', 'external')
    ,('2023-08-06', 'Sunday', 'West', 'Oregon', 'external')



UPDATE t
SET t.emp_01 = (SELECT COUNT(DISTINCT p.order_number) 
                FROM order_history p WITH (NOLOCK) 
                WHERE p.employee = 'emp_01')
FROM temp_table t WITH (NOLOCK) 
INNER JOIN order_history p WITH (NOLOCK)
ON (t.business_date = p.business_date AND t.region = p.region AND t.state = p.state)
WHERE t.business_date = p.business_date 
AND t.region = p.region 
AND t.state = p.state

Posted 6-Aug-23 22:29pm

Chris_List

Updated 7-Aug-23 1:49am

Richard Deeming

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2023-08-06T22:54:00

Your subquery for the count needs to include all of the conditions you want to match:

SQL

UPDATE 
    t
SET 
    emp_01 = (SELECT COUNT(DISTINCT p.order_number)
              FROM order_history p
              WHERE p.employee = 'emp_01'
              AND p.business_date = t.business_date
              AND p.region = t.region
              AND p.state = t.state)
FROM
    temp_table t
;

Also, avoid using NOLOCK, particularly when you're updating data. Many people seem to think it's a "go fast" flag, but it's actually a "corrupt my data" flag.
Bad habits : Putting NOLOCK everywhere[^]

CHill60 · Answer 2 · 2023-08-07T01:49:00

An alternative approach using GROUP BY and PIVOT avoiding all of those data inserts and not needing to create the temp table first

SQL

select business_date
	,datename(WEEKDAY, business_date) as day_of_week
	,region
	,[state]
	,wire
	,emp_01
	,emp_02
	,emp_03
	,emp_04
	,emp_05 
into #temp_table
from
(
	select order_number,business_date, employee, region, [state], wire, COUNT(*) as val
	from order_history
	group by order_number, business_date, employee, region, [state], wire
) src
PIVOT
(
	count(order_number) for employee in (emp_01, emp_02,emp_03, emp_04, emp_05)
) pvt;

N.B. This assumes there are only 5 employees. If there are more then you will probably need to generate some dynamc SQL to determine the columns names

How do I update multiple rows in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0