Mysql query syntax and examples - Revision history

Ted: /* ^ Simple MYSQL Statements */ - MYSQL count statement

2023-10-20T12:43:44Z

^ Simple MYSQL Statements: - MYSQL count statement

2021-10-17T21:11:30Z

^ Simple MYSQL Statements

2021-10-16T07:41:23Z

^ Simple MYSQL Statement

2021-10-16T05:07:41Z

^ Simple MYSQL Statement

2021-10-16T05:01:31Z

2021-10-16T05:00:35Z

OVERVIEW

2020-07-11T05:10:39Z

^ INNER JOIN syntax

2019-10-17T05:48:41Z

^ INNER JOIN syntax

2019-10-17T05:45:41Z

^ INNER JOIN syntax

2019-10-17T03:45:35Z

^ INNER JOIN syntax

@@ Line 29: / Line 29: @@
-Series of four table-scoped delete statements to remove products in a range of record IDs:
+Series of four table-scoped delete statements to remove products in a range of record IDs.  This four step delete process is necessary in Opencart versions 2.3.0.2 and 3.0.3.7 (likely other versions too) as a product must have at minimum the product attributes associated with it in each of these tables, in order to be active on the store front.
    > delete from oc_product_to_store where product_id > 2021500;
@@ Line 36: / Line 36: @@
    > delete from oc_product where product_id > 2021500;
 <!-- comment -->

← Older revision		Revision as of 21:11, 17 October 2021
Line 27:		Line 27:

	update oc_product as t1 set t1.quantity = 0 where product_id > 2021000 and product_id < 2021500;		update oc_product as t1 set t1.quantity = 0 where product_id > 2021000 and product_id < 2021500;
		+
		+
		+	Series of four table-scoped delete statements to remove products in a range of record IDs:
		+
		+	> delete from oc_product_to_store where product_id > 2021500;
		+	> delete from oc_product_to_category where product_id > 2021500;
		+	> delete from oc_product_description where product_id > 2021500;
		+	> delete from oc_product where product_id > 2021500;
		+

	<!-- comment -->		<!-- comment -->

@@ Line 14: / Line 14: @@
 <!-- comment -->
-== [[#top|^]] Simple MYSQL Statement ==
+== [[#top|^]] Simple MYSQL Statements ==
 Opencart disable all existing categories -- useful at initial cart set up time because Opencart ships with about forty example categories that don't necessarily apply to given end user's on-line store:
@@ Line 23: / Line 23: @@
    update oc_category as t1 set t1.status = 1 where t1.category_id > 100;
 <!-- comment -->

← Older revision		Revision as of 05:07, 16 October 2021
Line 19:		Line 19:

	update oc_category as t1 set t1.status = 0 where t1.status = 1;		update oc_category as t1 set t1.status = 0 where t1.status = 1;
		+
		+	Enable categories whose category_id is greater than 100:
		+
		+	update oc_category as t1 set t1.status = 1 where t1.category_id > 100;

	<!-- comment -->		<!-- comment -->

@@ Line 1: / Line 1: @@
 <center>
 <b>MYSQL Query Syntax And Examples</b><br />
-notes started 2017-08-28<br />
+local NN page started 2017-08-28<br />
 </center>
@@ Line 11: / Line 10: @@
 == [[#top|^]] OVERVIEW ==
-This page to hold Ted's notes on MYSQL syntax and practical examples.
+This page to hold notes on MYSQL syntax and practical examples.
 <!-- comment -->
@@ Line 23: / Line 22: @@
 <!-- comment -->
-== MYSQL Keywords and Clauses ==
+== [[#top|^]] MYSQL Keywords and Clauses ==
 . . .

@@ Line 90: / Line 90: @@
     mysql> select oc_category.category_id, oc_category_description.name from oc_category inner join oc_category_description on oc_category.category_id = oc_category_description.category_id;
@@ Line 219: / Line 225: @@
 </pre>
 === edit point ===

@@ Line 220: / Line 220: @@
 </pre>
 Using above examples, developed following insert statement to create table of all products which OpenCart table has "going" to store with id 1.  Difference in our work here is that we create a named table, which persists until we drop it, to hold the results of the first query.  We then use that table, whose name begins with 'z' to distinguish it from OpenCart's originally configured tables, to add desired new records to table "product to store":
@@ Line 241: / Line 243: @@
 Query OK, 11 rows affected (0.00 sec)
 Records: 22  Duplicates: 0  Warnings: 0
 mysql>

@@ Line 222: / Line 222: @@
 Using above examples, developed following insert statement to create table of all products which OpenCart table has "going" to store with id 1.  Difference in our work here is that we create a named table, which persists until we drop it, to hold the results of the first query.  We then use that table, whose name begins with 'z' to distinguish it from OpenCart's originally configured tables, to add desired new records to table "product to store":
 <pre>
 mysql> insert into `z-products-to-nn-test-store` (product_id, store_id) select product_id, '4' from oc_product_to_store where (store_id = 1);

← Older revision		Revision as of 03:45, 17 October 2019
Line 86:		Line 86:

	mysql> SELECT t1.product_id, t2.name FROM oc_product AS t1 INNER JOIN oc_product_description AS t2 ON t1.product_id = t2.product_id;		mysql> SELECT t1.product_id, t2.name FROM oc_product AS t1 INNER JOIN oc_product_description AS t2 ON t1.product_id = t2.product_id;
		+
		+	Another example of inner join, to obtain Opencart category names mapped one to one with unique category ids:
		+
		+	mysql> select oc_category.category_id, oc_category_description.name from oc_category inner join oc_category_description on oc_category.category_id = oc_category_description.category_id;