> At 21:27 -0500 5/23/02, Andrew Lietzow wrote: > >Dear MySQL wizards, > >I have two tables, call them a and b. The database structure is > > identical. I want the records in b to overwrite the records in (REPLACE > > INTO) a. > >This is the statement that does NOT work. > >REPLACE INTO a SELECT b.* FROM b WHERE a.field1 = b.field1 > >The field names in A are identical to B.
On Thursday 23 May 2002 09:33 pm, Paul DuBois wrote: > The SELECT part has to be a legal SELECT statement, which yours > isn't. (The WHERE part names two tables, but the FROM part names > only one.) Thank you for your reply, Paul. I now have two statements; one which works, one which does not. I do not know why the second will not work. *WORKS to create a new table* INSERT INTO a_copy SELECT a.field1, a.field2, .... a. field.6, b.field7 FROM a,b WHERE a.field1 = b.field1 My resultant table has the data from b that I wanted to put directly into a but could not (because of MySQL rules regarding manipulation of Two Tables?) and writing back to one of them. Is this the reason that I can't get this next statement to work (returns ERROR 1066 - Not unique table/alias. ) *Does NOT work to replace data in table a * REPLACE INTO a SELECT b.* FROM a, b WHERE a.field1 = b.field1 The FROM part now names two tables, as does the WHERE part. (I'm attempting to follow your curative recommendation). So what is wrong with this statement? I want to read records from one file, write them to another, stepping on the contents of the entire record based on a unique index key value. I have 2215 records with correct data (b) and the key matches a key in a file with 4935 records (a). Is this not feasible with MySQL until version 4.X? Maybe I can just say: REPLACE INTO a FROM b USING (field1) ??? TIA for any help... Andrew Lietzow --------------------------------------------------------------------- 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
