July 12, 2012 at 6:50 am
CREATE TABLE #Contact
(
ContactID INT,
FirstName NVARCHAR(100),
MiddleName NVARCHAR(100),
LastName NVARCHAR(100),
TotalDue MONEY
)
INSERT INTO #Contact(ContactID,FirstName,MiddleName,LastName,TotalDue)
SELECT A.ContactID,FirstName,MiddleName,LastName,TotalDue FROM AdventureWorks.Person.Contact A
INNER JOIN AdventureWorks.Sales.SalesOrderHeader B
ON A.ContactID=B.ContactID
CREATE TABLE #Sales
(
ContactID INT,
SalesOrderID INT,
SubTotal INT,
TaxAmt INT,
Freight INT
)
INSERT INTO #Sales(ContactID,Freight,SubTotal,TaxAmt,SalesOrderID)
SELECT ContactID,Freight,SubTotal,TaxAmt,SalesOrderID FROM AdventureWorks.Sales.SalesOrderHeader
UPDATE #Contact SET TotalDue=NULL
i want to update null values in TotalDue Column in #Contact table
using (Freight+SubTotal+TaxAmt) from #Sales Table
TotalDue=(Freight+SubTotal+TaxAmt)
ContactID in unique in #Contact table.
SalesOrderID is unique in #Sales table
how can i do this.Please help me,Want to accomplish using UPDATE statment
Thanks in advance
July 12, 2012 at 6:58 am
UPDATE c SET
TotalDue = s.Freight + s.SubTotal + s.TaxAmt
FROM #Contact c
INNER JOIN #Sales s ON s.ContactID = c.ContactID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2012 at 7:15 am
In my experience, Smash would need a slightly different approach if he needs the total due for each contact, because Chris' query will only return the total from one sale.
UPDATE c SET
TotalDue = s.TotalDue
FROM #Contact c
INNER JOIN (SELECT contactID,
SUM(s.Freight + s.SubTotal + s.TaxAmt) AS TotalDue
FROM #Sales
GROUP BY contactID) s ON s.ContactID = c.ContactID
July 12, 2012 at 7:21 am
July 12, 2012 at 7:30 am
Luis Cazares (7/12/2012)
In my experience, Smash would need a slightly different approach if he needs the total due for each contact, because Chris' query will only return the total from one sale.
UPDATE c SET
TotalDue = s.TotalDue
FROM #Contact c
INNER JOIN (SELECT contactID,
SUM(s.Freight + s.SubTotal + s.TaxAmt) AS TotalDue
FROM #Sales
GROUP BY contactID) s ON s.ContactID = c.ContactID
Thanks for the catch, Luis - even without Adventureworks installed here, that was evident from
SalesOrderID is unique in #Sales table
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2012 at 9:48 am
CELKO (7/12/2012)
... WHERE S.sales_order_id = D.sales_order_id;
Better duck now, Joe.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2012 at 10:06 am
ChrisM@Work (7/12/2012)
CELKO (7/12/2012)
... WHERE S.sales_order_id = D.sales_order_id;Better duck now, Joe.
Yes, Joe, duck. You may want to consider turning in your SQL Developer card.
Take a close look at your code. Looks to me that you are going to return a lot more than you expected. Also, I think it is time for you move out of the 80's and stop using ANSI-89 style joins and at least start using the ANSI-92 style joins.
Here is a rewrite of your code:
CREATE VIEW Sales_Summary (
sales_order_id,
contact_id,
order_tot
)
AS
WITH SalesDetail AS (
SELECT
sales_order_id,
SUM(order_qty * unit_price) SalesAmt
FROM
dbo.Sales_Details
GROUP BY
sales_order_id
)
SELECT
S.sales_order_id,
S.contact_id,
S.freight_amt + S.tax_amt + D.SalesAmt
FROM
dbo.Sales AS S
INNER JOIN SalesDetail AS D
ON (S.sales_order_id = D.sales_order_id);
July 12, 2012 at 10:23 am
By the way, Joe, I cleaned up your code a bit. Hope you don't mind.
CREATE TABLE Contacts(
contact_id CHAR(10) NOT NULL PRIMARY KEY,
contact_first_name VARCHAR(20) NOT NULL,
contact_middle_name VARCHAR(20) NOT NULL,
contact_last_name VARCHAR(20) NOT NULL
);
CREATE TABLE Sales(
sales_order_id CHAR(10) NOT NULL PRIMARY KEY,
contact_id CHAR(10) NOT NULL REFERENCES Contacts (contact_id),
tax_amt DECIMAL(8,2) NOT NULL CHECK (tax_amt >= 0.00),
freight_amt DECIMAL(8,2) NOT NULL CHECK (freight_amt >= 0.00)
);
CREATE TABLE Sales_Details(
sales_order_id CHAR(10) NOT NULL REFERENCES Sales (sales_order_id),
upc CHAR(13) NOT NULL,
contact_id CHAR(10) NOT NULL REFERENCES Contacts (contact_id),
unit_price DECIMAL(8,2) NOT NULL CHECK (unit_price >= 0.00),
order_qty INTEGER DEFAULT 1 NOT NULL CHECK (order_qty > 0),
PRIMARY KEY (sales_order_id, upc)
);
GO
July 16, 2012 at 7:45 am
CELKO (7/12/2012)
In RDBMS, we think in virtual tables – views, CTEs, derived tables, etc – and not a materialized one.
Actually, "It Depends". Referencing a CTE or a view more than once in a given query will cause that CTE or view to be executed once for each given reference. Further, since "virtual tables" don't actually exist, you can sometimes (many times) get yourself into some real performance problems unless you do actually materialize the data.
Yeah... I know... 50s "scratch" tape technology. There was a performance reason to use it back then just as there is now.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply