Writing SUBQUERIES and UPDATE SET multiple rows

http://blog.lysender.com/2010/03/updating-multiple-rows-in-mysql/

1 2 3 4 5 6

UPDATE     table_name SET     value = CONCAT(value, '09') WHERE     date_created = '2010-02-01';

The Scenario

What I’m trying to do is to append a suffix ’09′ for a certain column of all rows that met a certain criteria. For example I have these records:

Id	Value	Created
1	1000001	2010-02-01
2	1000002	2010-02-01
3	1000003	2010-02-01
4	1000004	2010-02-01
5	1000005	2010-02-09
6	1000006	2010-02-26

I wanted to append ’09′ to the end of the value column whose date created is ’2010-02-01′. I actually found the solution somewhere using different example. The example I’ve found was incrementing an integer value by one. In my problem, I need to append a year code for archiving purposes. So this is the code.

1 2 3 4 5 6

UPDATE     table_name SET     value = CONCAT(value, '09') WHERE     date_created = '2010-02-01';

And the result would make my data looks like below:

Id	Value	Created
1	100000109	2010-02-01
2	100000209	2010-02-01
3	100000309	2010-02-01
4	100000409	2010-02-01
5	1000005	2010-02-09
6	1000006	2010-02-26

2 python mysql updating multiple rows with different values





get all unique values in a column python mysql
DISTINCT
3 http://www.wellho.net/mouth/279_Getting-a-list-of-unique-values-from-a-MySQL-column.html

Getting a list of unique values from a MySQL column

Would you like to get a list of all the different values in a column? Use the DISTINCT keyword. Here's an example:

mysql> select * from train;

+-------+--------+-------------+-----+

| time  | length | destination | tid |

+-------+--------+-------------+-----+

| 07:05 |      1 | Salisbury   |   1 |

| 08:18 |      2 | Swindon     |   2 |

| 09:05 |      2 | Southampton |   3 |

| 05:45 |      1 | Swindon     |   4 |

| 10:35 |     12 | Plymouth    |  10 |

| 13:49 |      2 | Swindon     |   6 |

| 14:20 |      2 | Salisbury   |   7 |

| 17:07 |      1 | Swindon     |   8 |

| 18:18 |      1 | Salisbury   |   9 |

| 13:00 |      6 | Windsor     |  11 |

+-------+--------+-------------+-----+

10 rows in set (0.04 sec)



mysql> select distinct destination from train;

+-------------+

| destination |

+-------------+

| Salisbury   |

| Swindon     |

| Southampton |

| Plymouth    |

| Windsor     |

+-------------+

5 rows in set (0.08 sec)



mysql> 

See More on SQL module.


(written 2005-04-14, updated 2006-06-05)

!IMPORTANT !!!
4 SUBQUERIES
http://www.roseindia.net/mysql/mysql5/writing-subqueries.shtml

A subquery can be defined as a query within a query. In other words, any query results that we reuse in another query. Subquery is known as nestee queries or subselects also. Subqueries don?t include any new functionality but the queries are more readable

Writing Subqueries

      

A subquery can be defined as a query within a query. In other words, any query results that we reuse in another query. Subquery is known as nestee queries or subselects also. Subqueries don?t include any new functionality but the queries are more readable with using subqueries rather than of joins.

We will describe you the subqueries with the help of following tables : 

mysql> SELECT * FROM Client; +------+---------------+----------+ | C_ID | Name | City | +------+---------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 4 | R S P Ltd | Kolkata | | 5 | A T Ltd | Delhi | | 6 | D T Info | Delhi | +------+---------------+----------+ 6 rows in set (0.08 sec) mysql> SELECT * FROM Products; +---------+-------------+------+----------+ | Prod_ID | Prod_Detail | C_ID | price | +---------+-------------+------+----------+ | 111 | Monitor | 1 | 7000.00 | | 112 | Processor | 2 | 11000.00 | | 113 | Keyboard | 2 | 1200.00 | | 114 | Mouse | 3 | 500.00 | | 115 | CPU | 5 | 15500.00 | +---------+-------------+------+----------+ 5 rows in set (0.00 sec)

There are 3 basic types of subqueries in SQL: 

• Predicate Subqueries - extended logical constructs in the WHERE (and HAVING) clause.
• Scalar Subqueries - standalone queries that return a single value; they can be used anywhere a scalar value is used.
• Table Subqueries - queries nested in the FROM clause.

All subqueries must be enclosed in parentheses. 

Predicate Subqueries

Predicate Subqueries can be used in the HAVING and WHERE clause only because both are special logical construct. These subqueries must retrieve one column.

• IN Subquery  The IN subquery tests if a scalar values match with the single query column value in any subquery result row. The general syntax is :
    Value_1 [NOT] IN (query_1)
  In the following example we are getting the list of clients that are available in Products table also.Example : 

  mysql> SELECT * FROM Client WHERE C_ID IN -> (SELECT C_ID FROM Products); +------+---------------+----------+ | C_ID | Name | City | +------+---------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 5 | A T Ltd | Delhi | +------+---------------+----------+ 4 rows in set (0.00 sec)

  
  In the following example we are getting the list of clients that are not available in Products table also. Example : 
   

  mysql> SELECT * FROM Client WHERE C_ID NOT IN -> (SELECT C_ID FROM Products); +------+-----------+---------+ | C_ID | Name | City | +------+-----------+---------+ | 4 | R S P Ltd | Kolkata | | 6 | D T Info | Delhi | +------+-----------+---------+ 2 rows in set (0.01 sec)

  
• Quantified Subqueries
   
  A quantified subquery can use the all comparison operators for several types of tests. The general syntax is :
    Value_1 {=|>|<|>=|<=|<>} {ANY | ALL | SOME} (query_1)
    
  The comparison operator is used to compare value_1 to the single query column value from each subquery result row. If we are using ALL clause then must match the all rows in subquery, or subquery must be empty. If we are using ANY or SOME clause then must match at least one row in the subquery. Example :  

  mysql> SELECT * FROM Client WHERE C_ID= ANY(SELECT C_ID FROM Products); +------+---------------+----------+ | C_ID | Name | City | +------+---------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 5 | A T Ltd | Delhi | +------+---------------+----------+ 4 rows in set (0.00 sec)

 
• Exists Subqueries
  The EXISTS subquery is used to tests whether a subquery returns at least one row or a qualifying row exists. The general syntax is :
    Exists (query_1)
  Any EXISTS subquery should contain an outer reference. It must be a correlated subquery. Example : 
  

  mysql> SELECT * FROM Client -> WHERE EXISTS -> (SELECT * FROM Products WHERE Client.C_ID=Products.C_ID); +------+---------------+----------+ | C_ID | Name | City | +------+---------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 5 | A T Ltd | Delhi | +------+---------------+----------+ 4 rows in set (0.00 sec)

Scalar Subqueries

The Scalar Subquery is a subquery which returns a single value. A Scalar subquery can be used almost anywhere a single column value can be used. The subquery have to reference only one column in the select list. It must not retrieve more than one row. When subquery retrieve one row then the value of select list column becomes the value of the Scalar Subquery. Example : 

mysql> SELECT (SELECT Name FROM Client WHERE C_ID=1); +----------------------------------------+ | (SELECT Name FROM Client WHERE C_ID=1) | +----------------------------------------+ | A K Ltd | +----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT (SELECT C_ID FROM Products WHERE C_ID=2) FROM Client; ERROR 1242 (21000): Subquery returns more than 1 row mysql> SELECT (SELECT C_ID FROM Products WHERE C_ID=1) FROM Client; +------------------------------------------+ | (SELECT C_ID FROM Products WHERE C_ID=1) | +------------------------------------------+ | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | +------------------------------------------+ 6 rows in set (0.01 sec)

Table Subqueries

Table subqueries are used in the FROM Clause , replace the table name. These subqueries can have correlation name also. Example : 

mysql> SELECT Client.*,Price -> FROM Client, Products -> WHERE Client.C_ID=Products.C_ID -> AND Price>1000; +------+---------------+--------+----------+ | C_ID | Name | City | Price | +------+---------------+--------+----------+ | 1 | A K Ltd | Delhi | 7000.00 | | 2 | V K Associate | Mumbai | 11000.00 | | 2 | V K Associate | Mumbai | 1200.00 | | 5 | A T Ltd | Delhi | 15500.00 | +------+---------------+--------+----------+ 4 rows in set (0.06 sec)

Using Single Value Subqueries

Firstly we will start with a simple query : 

mysql> SELECT MAX(Price) FROM Products; +------------+ | MAX(Price) | +------------+ | 15500.00 | +------------+ 1 row in set (0.60 sec)

The above example retrieve only a single value and its representing the maximum Price of the Product. In this example we used a MySQL Function MAX() that finds the greatest values in a specified column. 

Single ? value subqueries is used to return a single column value and then they are typically used for comparison. For Example :

mysql> SELECT * FROM Client c,Products p WHERE c.C_ID=p.C_ID -> AND p.Price=(SELECT MAX(Price) FROM Products); +------+---------+-------+---------+-------------+------+----------+ | C_ID | Name | City | Prod_ID | Prod_Detail | C_ID | price | +------+---------+-------+---------+-------------+------+----------+ | 5 | A T Ltd | Delhi | 115 | CPU | 5 | 15500.00 | +------+---------+-------+---------+-------------+------+----------+ 1 row in set (0.02 sec)

In the above example we are getting the detail of products that have the highest price and the client details also.