MS SQL Server

If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives you a good example:

INSERT INTO ggl_links SELECT id+500, REVERSE(url),

notes, counts, created FROM ggl_links

GO

(3 row(s) affected)

SELECT * FROM ggl_links

GO

id url notes counts created

101 www.rendc.org NULL 0 2006-04-30

102 www.rendc.org/html NULL 0 2007-05-19

103 www.rendc.org/sql NULL NULL 2007-05-19

601 www.rendc.org/seo NULL 0 2006-04-30

602 www.rendc.org/xml NULL 0 2007-05-19

603 www.rendc.org/JavaScript_Guide NULL NULL 2007-05-19

As you can see, "INSERT INTO ... SELECT ..." is powerful statement. you can use it build up data in tables quickly.

If you have an existing table and you want to change the table name, you can use the "sp_rename ... 'OBJECT'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT (including tables). The tutorial example below shows you how to rename a table:

sp_rename 'tip', 'faq', 'OBJECT'

GO

Caution: Changing any part of an object name could break

scripts and stored procedures.

SELECT name, type_desc, create_date FROM sys.tables

GO

name type_desc create_date

faq USER_TABLE 2007-05-19 23:05:43.700

tipBackup USER_TABLE 2007-05-19 23:25:23.357

You can also rename a table with on the Object Explorer window of SQL Server Management Studio. See tutorials on rename table columns.

If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by column names in the expressions. The tutorial exercise below shows you a good example:

SELECT * FROM ggl_links WHERE id >= 500

GO

id url notes counts created

601 www.rendc.org Wrong 9 2006-04-30

602 www.rendc.org/html Wrong 9 2007-05-21

603 www.rendc.org/sql Wrong 9 2007-05-23

UPDATE ggl_links SET id = id+200, counts = id*2

WHERE id >= 500

GO

(3 row(s) affected)

SELECT * FROM ggl_links WHERE id >= 500

GO

id url notes counts created

801 www.rendc.org Wrong 1202 2006-04-30

802 www.rendc.org/html Wrong 1204 2007-05-19

803 www.rendc.org/sql Wrong 1206 2007-05-19

This statement increased values in the id column by 200. It also updated the counts column with the newly increased id value.

If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The tutorial script below shows a good example:

SELECT * FROM ggl_links WHERE id = 101

GO

id url notes counts created

101 www.rendc.org NULL 0 2006-04-30

UPDATE ggl_links SET counts = 999, notes = 'Good.'

WHERE id = 101;

GO

(1 row(s) affected)

SELECT * FROM ggl_links WHERE id = 101

GO

id url notes counts created

101 www.rendc.org Good. 999 2006-04-30

As you can see, the SET clause takes column and value pairs to provide new values, while the WHERE clause defines which row to apply the update.

If you want to delete an existing row from a table, you can use the DELETE statement with a WHERE clause to identify that row. Here is good sample of DELETE statements:

-- insert a row for this test

INSERT INTO ggl_links (url, id)

VALUES ('www.google.com', 301)

GO

(1 row(s) affected)

-- view the inserted row

SELECT id, url, notes, counts FROM ggl_links

WHERE id = 301

GO

id url notes counts

301 www.google.com NULL NULL

-- delete one row

DELETE FROM ggl_links WHERE id = 301

GO

(1 row(s) affected)

-- try to view the deleted row

SELECT id, url, notes, counts FROM ggl_links

WHERE id = 301

no rows

Row with id of 301 is truly deleted.

The answer is NO. The order of columns in the SET clause of the UPDATE statement is NOT important. You probably already noticed from the previous tutorial. There is a BIG DIFFERENCE among SQL Server, MySQL and Oracle on update multiple columns with previous values:

* SQL Server provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important

* MySQL provides you the updated values on columns names used in new value expressions. So the order of columns in the SET clause is important.

* Oracle provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important

Here is a good tutorial exercise:

SELECT * FROM ggl_links

-- Check the old values

SELECT * FROM ggl_links WHERE url = 'www.rendc.org'

GO

id url notes counts created

101 www.rendc.org Good. 999 2006-04-30

-- Update "id" before "counts"

UPDATE ggl_links SET id = id+200, counts = id*2

WHERE url = 'www.rendc.org'

GO

(1 row(s) affected)

If you want to update values in one table with values from another table, you can use a subquery as an expression in the SET clause. The subquery should return only one row for each row in the update table that matches the WHERE clause. The tutorial exercise below shows you a good example:

-- Create another table

CREATE TABLE ggl_rates (id INTEGER,

comment VARCHAR(16))

Go

-- Insert some rows in the new table

INSERT INTO ggl_rates VALUES (101, 'The best')

Go

INSERT INTO ggl_rates VALUES (102, 'Well done')

GO

INSERT INTO ggl_rates VALUES (103, 'Thumbs up')

Go

-- Update ggl_links with values from ggl_rates

UPDATE ggl_links SET notes = (

SELECT comment FROM ggl_rates

WHERE ggl_rates.id = ggl_links.id

)

WHERE id > 0 AND id < 110

GO

(3 row(s) affected)

-- View the updated values

SELECT * FROM ggl_links

WHERE id > 0 AND id < 110

GO

id url notes counts created

101 www.rendc.org The best 999 2006-04-30

102 www.rendc.org/html Well done 0 2007-05-19

103 www.rendc.org/sql Thumbs up NULL 2007-05-19

Note that if column names are confusing between the inner table and the outer table, you need to pref

If the WHERE clause in an UPDATE statement matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example:

SELECT * FROM ggl_links WHERE id >= 500

GO

id url notes counts created

601 ww.rendc.org NULL 0 2006-04-30

602 ww.rendc.org/html NULL 0 2007-05-19

603 ww.rendc.org/sql NULL NULL 2007-05-19

SELECT * FROM ggl_links

UPDATE ggl_links SET counts = 9, notes = 'Wrong'

WHERE id >= 500

(3 row(s) affected)

SELECT * FROM ggl_links WHERE id >= 500

GO

id url notes counts created

601 ww.rendc.org Wrong 9 2006-04-30

602 ww.rendc.org/html Wrong 9 2007-05-19

603 ww.rendc.org/sql Wrong 9 2007-05-19

The UPDATE statement updated 3 rows with the same new values.

If you use a subquery to assign new values in the SET clause in an UPDATE statement, and the subquery returns no rows for an outer row, SQL Server will provide a NULL value to the SET clause. The tutorial exercise below shows you a good example:

-- insert a new row

INSERT INTO ggl_links (id, url, notes)

VALUES (0, 'www.rendc.org', 'Number one')

GO

(1 row(s) affected)

-- view old values

SELECT * FROM ggl_links WHERE id = 0

GO

id url notes counts created

0 www.rendc.org Number one NULL 2007-05-23

-- make sure there is no matching row in fyi_rates

SELECT * FROM ggl_rates WHERE id = 0

GO

0 rows

-- update a subquery returning no rows

UPDATE ggl_links SET notes = (

SELECT comment FROM ggl_rates

WHERE ggl_rates.id = ggl_links.id

)

WHERE id = 0

(1 row(s) affected)

-- view new values

SELECT * FROM ggl_links WHERE id = 0

GO

id url notes counts created

0 www.rendc.org NULL NULL 2007-05-23

Column "notes" gets updated with NULL if there is no return rows in the subquery.

You can delete multiple rows from a table in the same way as deleting a single row, except that the WHERE clause will match multiple rows. The tutorial exercise below deletes 3 rows from the ggl_links table:

-- view rows to be deleted

SELECT id, url, notes, counts FROM ggl_links

WHERE id > 300

GO

id url notes counts

801 www.rendc.org Wrong 1202

802 www.rendc.org/html Wrong 1204

803 www.rendc.org/sql Wrong 1206

-- delete multiple rows

DELETE FROM ggl_links WHERE id > 300

GO

(3 row(s) affected)

-- try to view the deleted row

SELECT id, url, notes, counts FROM ggl_links

WHERE id > 300

GO

no rows

If you want to query from two tables with an inner join, you can use the INNER JOIN ... ON clause in the FROM clause. The tutorial exercise below creates another testing table and returns output with an inner join from two tables: ggl_links and ggl.rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:

CREATE TABLE ggl_rates (id INTEGER,

comment VARCHAR(16))

GO

INSERT INTO ggl_rates VALUES (101, 'The best')

GO

INSERT INTO ggl_rates VALUES (102, 'Well done')

GO

INSERT INTO ggl_rates VALUES (103, 'Thumbs up')

GO

INSERT INTO ggl_rates VALUES (204, 'Number 1')

GO

INSERT INTO ggl_rates VALUES (205, 'Not bad')

GO

INSERT INTO ggl_rates VALUES (206, 'Good job')

GO

INSERT INTO ggl_rates VALUES (207, 'Nice tool')

GO

SELECT ggl_links.id, ggl_links.url,

ggl_rates.comment FROM ggl_links

INNER JOIN ggl_rates ON ggl_links.id = ggl_rates.id

GO

id url comment

101 www.rendc.org The best

102 www.rendc.org/html Well done

103 www.rendc.org/sql Thumbs up

Note that when multiple tables are used in a query, column names need to be prefixed with table names in case the same colu

If a subquery is used in a UPDATE statement, it must return exactly one row for each row in the update table that matches the WHERE clause. If it returns multiple rows, SQL Server will give you an error message. To test this out, you can try the following tutorial exercise:

-- insert two rows to ggl_rates

INSERT INTO ggl_rates VALUES (0, 'Number 1')

GO

INSERT INTO ggl_rates VALUES (0, 'Number 2')

GO

-- make sure there are 2 match rows

SELECT * FROM ggl_rates WHERE id = 0

GO

id comment

0 Number 1

0 Number 2

-- update with subquery that returns 2 rows

UPDATE ggl_links SET notes = (

SELECT comment FROM ggl_rates

WHERE ggl_rates.id = ggl_links.id

)

WHERE id = 0

GO

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted

when the subquery follows =, !=, <, <= , >, >= or when

the subquery is used as an expression.

The statement has been terminated.

It is clear that we are using subquery as an expression, and it must return 0 or 1 row. Otherwise, we will get an error.

If you want to query from two tables with a right outer join, you can use the RIGHT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a right outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:

SELECT l.id, l.url, r.comment FROM ggl_links l

RIGHT OUTER JOIN ggl_rates r ON l.id = r.id

GO

id url comment

101 www.rendc.org The best

102 www.rendc.org/html Well done

103 www.rendc.org/sql Thumbs up

NULL NULL Number 1

NULL NULL Not bad

NULL NULL Good job

NULL NULL Nice tool

Note that a right outer join may return extra rows from the second (right) table that do not satisfy the join condition. In those extra rows, columns from the first (left) table will be given null values.

The extra rows returned from the right outer join in this example represents rates that have no links in the above example.

If you want to query from two tables with a left outer join, you can use the LEFT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a left outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:

SELECT l.id, l.url, r.comment FROM ggl_links l

LEFT OUTER JOIN ggl_rates r ON l.id = r.id

GO

id url comment

101 www.rendc.org The best

102 www.rendc.org/html Well done

103 www.rendc.org/sql Thumbs up

104 www.google.com NULL

105 www.yahoo.com NULL

106 www.php.net NULL

107 www.mysql.com NULL

Note that a left outer join may return extra rows from the first (left) table that do not satisfy the join condition. In those extra rows, columns from the second (right) table will be given null values.

The extra rows returned from the left outer join in this example represents links that have no rates in the above example.

If you want to query from two tables with a full outer join, you can use the FULL OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a full outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:

SELECT l.id, l.url, r.comment FROM ggl_links l

FULL OUTER JOIN ggl_rates r ON l.id = r.id

GO

id url comment

101 www.rendc.org The best

102 www.rendc.org/html Well done

103 www.rendc.org/seo Thumbs up

104 www.google.com NULL

105 www.yahoo.com NULL

106 www.php.net NULL

107 www.mysql.com NULL

NULL NULL Number 1

NULL NULL Not bad

NULL NULL Good job

NULL NULL Nice tool

As you can see, an full outer join returns 3 groups of rows:

* The rows from both tables that satisfy the join condition.

* The rows from the first (left) table that do not satisfy the join condition.

* The rows from the second (right) table that do not satisfy the join condition.

A subquery is a SELECT statement used as part of the selection criteria of the main SELECT statement. The subquery specified in the WHERE clause will be evaluated repeated on each row of the selection base table. The output of the subquery will be used in the final evaluation of the criteria. Usually, subqueries are used in the following Boolean operations:

* "expression IN (subquery)" - True if the expression matches one of the returned values from the subquery.

* "expression NOT IN (subquery)" - True if the expression does not match any of the returned values from the subquery.

* "EXISTS (subquery)" - True if the subquery returns one or more rows.

* "NOT EXISTS (subquery)" - True if the subquery returns no rows.