想解決update卻沒該column的錯誤

想解決update卻沒該column的錯誤
避免一開啟程式就出錯
一開始研究在SQL command下手
但無法避開錯誤
後來發現在client端擋掉query錯誤即可

	//wrong code
	//want to solve it in mySQL command...
	UPDATE [table]
	SET [column]='100'
	WHERE gName='00102003'
	AND EXISTS(SELECT [column]
	FROM [table]
	WHERE gName='00102003'
	)
	Limit 1

view raw gistfile1.txt hosted with ❤ by GitHub

	//correct code
	//in BCB side, make sure query is correct
	SQL = "SELECT [column] From [table] Limit 1";
	mysql_query(mysql_main,SQL.c_str());
	res = mysql_store_result(mysql_main);
	if(res!=NULL) //<----only check first step of query is ok
	if((row = mysql_fetch_row(res)) != NULL)
	{
	....
	}

view raw gistfile1.txt hosted with ❤ by GitHub

注意:
1. Limit 1 必寫
2. exist 的用法
https://www.techonthenet.com/mysql/exists.php
3. 從res就要擋掉了，不然到row會大出錯

MySQL: EXISTS Condition

This MySQL tutorial explains how to use the MySQL EXISTS condition with syntax and examples.

Description

The MySQL EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the EXISTS condition in MySQL is:

WHERE EXISTS ( subquery );

Parameters or Arguments

subquery

A SELECT statement that usually starts with SELECT * rather than a list of expressions or column names. MySQL ignores the list of expressions in the subquery anyways.

Note

• SQL statements that use the EXISTS Condition in MySQL are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS Condition.

Example - With SELECT Statement

Let's look at a simple example.

The following is a SELECT statement that uses the MySQL EXISTS condition:

SELECT *
FROM customers
WHERE EXISTS (SELECT *
              FROM order_details
              WHERE customers.customer_id = orders.customer_id);

This MySQL EXISTS condition example will return all records from the customers table where there is at least one record in the order_details table with the matching customer_id.

Example - With SELECT Statement using NOT EXISTS

The MySQL EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
                  FROM order_details
                  WHERE customers.customer_id = orders.customer_id);

This MySQL EXISTS example will return all records from the customers table where there are no records in the order_details table for the given customer_id.

Example - With INSERT Statement

The following is an example of an INSERT statement that uses the MySQL EXISTS condition:

INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);

Example - With UPDATE Statement

The following is an example of an UPDATE statement that uses the MySQL EXISTS condition:

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT *
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

Example - With DELETE Statement

The following is an example of a DELETE statement that uses the MySQL EXISTS condition:

DELETE FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);