|
|
Suppose that we have to update delivery price in Invoices
table. We'll use Update JOIN together with scalar UDF function
fn_getdeliveryprice(@country, @city).
Pay attention that alternative way to do the same update is to use CURSOR
as described in Using CURSOR in stored procedure (1)
example.
USE [colombo]
GO
ALTER TABLE dbo.Invoice ADD
DlvPrice FLOAT;
GO
-- this select retrives delivery price for every invoice
SELECT t1.DocNo,
dbo.fn_getdeliveryprice (t1.Country, t1.City) AS DlvPrice
FROM
(SELECT a.DocNo, b.Country, b.City
FROM dbo.Invoice AS
a INNER JOIN dbo.Customers AS b
ON a.CustomerID = b.CustomerID
GROUP BY a.DocNo, b.Country, b.City)
AS t1;
GO
|
Results will look like this:
| DocNo | DlvPrice |
| 1 | 28.5 |
| 2 | 6 |
| 3 | 45 |
| 4 | 8.15 |
| .. | .. |
USE [colombo]
GO
/* update column DlvPrice in Invoice table with the
calculated price using join */
UPDATE t1
SET t1.DlvPrice =
dbo.fn_getdeliveryprice(t2.Country, t2.City)
FROM Invoice AS
t1 INNER JOIN
(SELECT a.DocNo, b.Country, b.City
FROM dbo.Invoice AS
a INNER JOIN dbo.Customers AS b
ON a.CustomerID = b.CustomerID
GROUP BY a.DocNo, b.Country, b.City)
AS t2;
GO
-- see results:
SELECT DocNo, DlvPrice FROM
dbo.Invoice;
|
Results will look like this:
| DocNo | DlvPrice |
| 1 | 28.5 |
| 2 | 6 |
| 3 | 45 |
| 4 | 8.15 |
| .. | .. |
-- remove added column:
ALTER TABLE dbo.Invoice
DROP COLUMN DlvPrice;
GO
|
|