Tuesday, November 24, 2009
update one table with data from another table
Table Structures and values:
TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)
The foreign key relationship between the two tables is based on A.a = B.a1
The data in these 2 tables is as follows:
I. TableA
a b c d
1 x y z
2 a b c
3 t x z
II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50
The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.
Oracle:
UPDATE TABLEASET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/
Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z
SQL Server:
UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO
Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.
Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z
Happy writing SQL
Thursday, July 9, 2009
Divide by zero error encountered
The workaround for the Divide by zero error encountered. error is by using the NULLIF Function as the the example below demonstrates. The NULLIF function returns the first expression if the two expressions are not equivalent otherwise a NULL is returned.
Usage: SELECT NULLIF(col1, 0)
When col1 = 0 a NULL will be returned.
Example
CREATE TABLE #Products
(
Product VARCHAR(10),
Price DECIMAL(7, 2),
CostPrice DECIMAL(7, 2)
)
INSERT #Products SELECT 'A4 Books', 12.00, 6.00
INSERT #Products SELECT 'Pens', 0.10, 0.05
INSERT #Products SELECT 'Pencils', 0, 0.05
SELECT Product, (CostPrice / Price) * 100 AS PercentProfit FROM #Products
Will Produce the following Error:
(3 row(s) affected)
Server: Msg 8134, Level 16, State 1, Line 14
Divide by zero error encountered.
---------------------------------------------------------------------
SELECT Product, ((CostPrice/NULLIF(Price, 0))*100) AS PercentProfit
FROM #Products
Returns:
Product PercentProfit
---------- -----------------------
A4 Books 50.0000000000
Pens 50.0000000000
Pencils NULL
(3 row(s) affected)
Friday, June 26, 2009
SQL Query to get customers with one product
You may want to find Customer that only has only bought one particular product in the Sales table
You will know the Product_ID is the in the Products table, in this example we use ProductID=10
In this example for each client you will find the lowest value Product_ID and the highest value Product_ID, then filters rows to those where Product_ID is the same as the variable.
--Written by Rishka Booran-Johnson
-- Get Customers with only one product
CREATE TABLE #Customers (
customer_ID int,
firstname varchar(30),
lastname varchar(30)
)
CREATE TABLE #Sales (
Transaction_ID int,
Customer_ID int,
Product_ID int,
Sale_date datetime
)
CREATE TABLE #Products (
Product_ID int,
Product_name varchar(30),
Product_description varchar(50)
)
INSERT #customers SELECT 1, 'john', 'smith'
INSERT #customers SELECT 2, 'sally', 'johnson'
INSERT #customers SELECT 3, 'joe', 'bloggs'
INSERT #Products SELECT 1, 'bicycles', ''
INSERT #Products SELECT 2, 'trains', ''
INSERT #Products SELECT 3, 'dolls', ''
INSERT #Sales SELECT 1, 1, 2, '2009-06-01'
INSERT #Sales SELECT 2, 1, 1, '2009-06-08'
INSERT #Sales SELECT 2, 1, 1, '2009-06-07'
INSERT #Sales SELECT 3, 2, 2, '2009-06-12'
INSERT #Sales SELECT 4, 2, 2, '2009-06-05'
INSERT #Sales SELECT 5, 3, 3, '2009-06-06'
INSERT #Sales SELECT 6, 3, 2, '2009-06-01'
INSERT #Sales SELECT 7, 3, 1, '2009-06-03'
declare
@vproduct_ID int
set @vproduct_ID = 2; -- this would be your input variable for trains
SELECT s.customer_ID, c.firstname, c.lastname, MIN(s.product_ID), MAX(s.product_ID)
FROM #sales s, #customers c
WHERE s.customer_ID = s.customer_ID
AND s.customer_ID = c.customer_ID
GROUP BY s.customer_ID, c.firstname, c.lastname
HAVING MIN(s.product_ID) = @vproduct_ID and MAX(s.product_ID) = @vproduct_ID
DROP TABLE #Customers
DROP TABLE #Sales
DROP TABLE #Products
Wednesday, June 24, 2009
How to check database growth
Sometimes you’ll want to know how much and how fast your database has been growing. Usually you will have a document that tells you the size information on all the databases, but this is not always the case in the real world. Considering that you perform regular backups, the following script can give you a estimated idea of growth over time of your database.
select
BackupDate = convert(varchar(10),backup_start_date, 111),
SizeInGigs=floor( backup_size/1024000000)
from msdb..backupset
where
database_name = ‘DatabaseName’
and type = ‘d’
order by
backup_start_date desc
Furthermore the Backup type can be used for the following types as well , making it easier to track other types of objects:
- D = Database
- I = Differential database
- L = Log
- F = File or filegroup
- G =Differential file
- P = Partial
- Q = Differential partial
Tuesday, April 14, 2009
SQL.. the beginning.
The first time I ever saw a database was in Paradox (database), in 1995. It was a huge learning curve, but I quickly learned what tables, columns and rows were. Then I learned how to build relationships and write SQL queries. (SQL is short for Structured Query Language)
This is how I then figured it was put together.
Every database has collection of one or more tables.
Tables are made up of rows and columns.
The rows contain the data and columns describe the format of the data, namely data type, for that column.
Imagine a telephone directory, information of a person’s first name, surname , address and ph number, this is how the basic table would be constructed:
Table: Phone_Directory
| FirstName | LastName | Address | Phone |
| Jake | Black | 123 Round Road, CA | 123 321-1234 |
| Sonia | Coke | 19 Sailfish Road, WA | 232 555-5555 |
| Mel | Brown | 32 71st Ave, NY | 614 555-5432 |