CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

How to update two tables at the same time of using two separate Updates?

Here's my case scenario; I'm looking to minimize the amount of steps that I'm going through. Specifically, I'm updating a master table with the values of a child table, but then I'm updating the child table to know which the previous updates were.

Anyways, in my case, I have to aggregate the SalesId that are common in both tables, and then order all Values.

AutoTable:

SalesId     Value
Joe         10
Rick        5
Jean        8
Bob         19
Julian      12

ManualTable:

SalesId     Value     WasUpdated
Vick        18
Rick        20
Mary        15
July        11

I would need to order all the sales from both tables. There's one SalesId (Rick) that's in both tables:

Sales     Total     Auto     Manual
Rick      25        5        20
Bob       19        19       0
Vick      18        0        18
Mary      15        0        15
Julian    12        12       0
July      11        0        11
Joe       10        10       0
Jean      8         8        0

In order to get this result, I was going to use the following logic. As you can see, step 3 & 4 are somewhat repetitive; In my case, I'm updating the @DowntimeTable for Rick's case, but then I'm updating ManualTable so that I know that I already dealt with Rick and there's no need to insert (step 5).

It's possible that some scripts don't run properly. The purpose of the question is to eliminate steps.

-- Create @DowntimeTable table

declare @DowntimeTable table ( Sales varchar(20), Auto int, Manual int, Total int )

-- Add AutoTable to @DowntimeTable

INSERT INTO @DowntimeTable (Sales, Auto) Select sales, auto from AutoTable

-- Update @DowntimeTable.Manual with matching ManualTable.Sales (ie Rick)

Update @DowntimeTable set Manual = ManualTable.Value 
from ManualTable inner join @DowntimeTable td on 
ManualTable.Sales = @DowntimeTable.sales

-- Set ManualTable.WasUpdate true for rows updated in previous UPDATE (ie Rick)

Update ManualTable 
set WasUpdated = 1 from ManualTable inner join @DowntimeTable on 
ManualTable.Sales = @DowntimeTable.sales

-- Insert rest of ManualTable

INSERT INTO @DowntimeTable (Sales, Manual) 
Select sales, Manual from ManualTable where WasUpdated = 0

UPDATE @DowntimeTable set Total = Manual + Auto

So, my question is: Is it possible to group steps 3 & 4 into one sql script? Maybe update AutoTable automatically once @DowntimeTable is updated?

Thanks.

Upvotes: 2

Views: 109

Answers (1)

mxix
mxix

Reputation: 3659

That's not a very clear way to ask... just post the code and add the comments not comments with code.

I know i'm not updating anything but at first it looks like you are trying to do updates only to output something like this?

select 
    ISNULL(AT.SalesId,MT.SalesId) [Sales],
    ISNULL(AT.Value,0)+ISNULL(MT.Value,0) [Total],
    ISNULL(AT.Value,0) [Auto],
    ISNULL(MT.Value,0) [Manual]
from AutoTable AT
full outer join ManualTable MT ON
    AT.SalesId = MT.SalesId

Upvotes: 1

Related Questions