Difference between revisions of "Mysql query syntax and examples"

From Wiki at Neela Nurseries
Jump to: navigation, search
(2017-08-28 MON - Ted starting MYSQL query syntax and example notes, on Neela Nurseries wiki.)
 
m (EXAMPLES)
Line 14: Line 14:
 
== EXAMPLES ==
 
== EXAMPLES ==
  
MYSQL's <code>IN</code> clause, useful to combine <code>OR</code> statements in queries and useful to express subqueries.  Example:
+
MYSQL's <code>IN</code> clause, useful to combine <code>OR</code> statements in queries and useful to express subqueries.  The following example query shows how to, in OpenCart's database tables, change the status of products which associated with a particular category.  Product statae live in one table, and numeric identifiers (ids) associated with products live in another table.  MYSQL's <code>IN</code> clause allows us to update product statae in one table, for those prooducts whose ids match conditions in another table.  This is a very simple use of MYSQL <code>IN</code> clause:
  
 +
 +
<i>Code example x - MYSQL IN clause:</i>
  
 
<pre>
 
<pre>

Revision as of 20:44, 28 August 2017

MYSQL Query Syntax And Examples
notes started 2017-08-28
by Ted Havelka


OVERVIEW

EXAMPLES

MYSQL's IN clause, useful to combine OR statements in queries and useful to express subqueries. The following example query shows how to, in OpenCart's database tables, change the status of products which associated with a particular category. Product statae live in one table, and numeric identifiers (ids) associated with products live in another table. MYSQL's IN clause allows us to update product statae in one table, for those prooducts whose ids match conditions in another table. This is a very simple use of MYSQL IN clause:


Code example x - MYSQL IN clause:


    mysql> update oc_product as t1 set t1.status = 0 where t1.product_id in (select t2.product_id from oc_product_to_category as t2 where t2.category_id = 60);
    Query OK, 18 rows affected (0.00 sec)
    Rows matched: 18  Changed: 18  Warnings: 0

    mysql> update oc_product as t1 set t1.status = 0 where t1.product_id in (select t2.product_id from oc_product_to_category as t2 where t2.category_id = 64);
    Query OK, 6 rows affected (0.00 sec)
    Rows matched: 6  Changed: 6  Warnings: 0

mysql>