Kittiphum,
Friday, December 06, 2002, 12:19:47 PM, you wrote:
KW> REALFROM: Kittiphum Worachat <[EMAIL PROTECTED]>
KW> On Wed, 4 Dec 2002 17:38:00 +0200, Victoria Reznichenko wrote:
>>Kittiphum,
>>Wednesday, December 04, 2002, 1:49:08 PM, you wrote:
>>
>>K> This query can find the record in Table1 that not found in Table2
>>
>>K> SELECT Table1.ID FROM Table1 LEFT JOIN Table2 Using(ID)
>>K> WHERE Table2.ID IS NULL
>>
>>K> and what query that use to delete the record in Table1 that not
>>found
>>K> in Table2 such as
>>
>>K> Delete from Table1 where Table1.ID in
>>K> SELECT Table1.ID FROM Table1 LEFT JOIN Table2 Using(ID)
>>K> WHERE Table2.ID IS NULL
>>
>>K> because of MySQL ver 3.23.XX not support sub select while we
>>still
>>K> use 3.23.XX
>>
>>In 3.23. you can't do it with single SQL statement. You can use
>>programming language or use CONCAT() function as described here:
>>http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html
>>
KW> Yes I use this method before but I don't want to resend the first
KW> result from conncat() back to MySQL to perform the second query again
KW> can I place the result somewhere may be some memory or some file on
KW> MySQL server so MySQL can use it automatic for the secod query
Create temporary table and insert data that you didn't want to delete
from table1. Delete all rows from table1. replace data from the
temporary table to the table1.
>>K> and one more question
>>
>>K> How to merge table with varchar type not char type (because if
>>table
>>K> contain varchar column we can't merge them but with table that
>>only
>>K> have char column it work)
>>
>>It works fine for me. If it doesn't work for you, please, provide
>>some more info.
>>
KW> I still not know what error because I do the same query that used to
KW> be error but it work today????
KW> but if I specify insert method I got error like this
KW> Error: 1064 - You have an error in your SQL syntax near
KW> 'INSERT_METHOD=LAST'
KW> I try to change to INSERT_METHOS=FIRST i got the same error
KW> so I change to
KW> CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
KW> CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
KW> INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
KW> INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
KW> CREATE TABLE total (a INT AUTO_INCREMENT PRIMARY KEY, message
KW> CHAR(20))
KW> TYPE=MERGE UNION=(t1,t2);
KW> it work but when I insert data into merge table i got
KW> Error: 1031 - Table handler for 'total' doesn't have this option
KW> What happen and how to solve this.
>From the MySQL manual:
- You can only SELECT, DELETE, and UPDATE from the collection of tables.
- Optionally you can specify with INSERT_METHOD if you want insert
for the MERGE table to happen in the first or last table in the
UNION list. If you don't specify INSERT_METHOD or specify NO, then
all INSERT commands on the MERGE table will return an error.
- If you want to insert data in a MERGE table, you have to specify
with INSERT_METHOD into with table the row should be inserted. See
section 7.2 MERGE Tables. This option was introduced in MySQL 4.0.0.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
