List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 26 1999 5:14pm
Subject:Re: replacing a table by another one
View as plain text  
At 12:07 PM -0500 9/26/99, Pierre Moret wrote:
>Hi,
>
>I'd like to populate a temporary table 'Object_in', and then replace
>table 'Object' with Object_in. Both tables have the same structure.
>
>What I'm currently doing is following:
>
>1. # lock the _in table
>"LOCK TABLES Object_in WRITE"
>
>2. # insert the objects
>a bunch of "INSERT LOW_PRIORITY INTO Object_in VALUES ..." are executed
>
>3. # lock all tables
>"LOCK TABLES Object_in WRITE, Object WRITE"
>
>4. # delete 'real' table
>"DELETE FROM Object"
>
>5. # populate the 'real' table
>"INSERT INTO Object SELECT <list of fields> FROM Object_in"
>
>6. # unlock all tables
>"UNLOCK TABLES"
>
>7. # optimizing table
>"OPTIMIZE TABLE Object"
>
>
>Is there a better way to achieve the same result? Table 'Object' has to
>be available as much as possible.


Or drop the original table, then use ALTER TABLE to rename the new
one to the original name.

--
Paul DuBois, paul@stripped
Thread
replacing a table by another onePierre Moret26 Sep
  • Re: replacing a table by another onePaul DuBois26 Sep
    • Re: replacing a table by another onePierre Moret26 Sep
      • Re: replacing a table by another oneMichael Widenius26 Sep
        • Re: replacing a table by another oneJames Manning26 Sep
          • Re: replacing a table by another oneMichael Widenius27 Sep
    • Re: replacing a table by another onePierre Moret26 Sep
  • Re: replacing a table by another oneRalph Graulich26 Sep