Moving Records 1

[snowboardr]

I know there is a way to do it but for the life of me I cant think of the SQL to move certain Records from 1 table to another table with the same table structure.

Regards,
Jason

 

[r937]

move or copy?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TonyGroves]

I don't know of any way to do it in one query.
You could use something like:
[tt]
LOCK TABLE table1;
INSERT table2 SELECT * FROM table1 WHERE field1='abc';
DELETE table1 WHERE field1='abc';
UNLOCK tables;
[/tt]

 

[TonyGroves]

Sorry, that should be DELETE FROM ...

 

[snowboardr]

Move... I at work, but ill test tony's suggestion tomorrow.



www.sitesd.com
ASP WEB DEVELOPMENT

 

[snowboardr]

Move... I am not at work, but ill test tony's suggestion tomorrow.


www.sitesd.com
ASP WEB DEVELOPMENT

 

[TonyGroves]

I'm not thinking straight today ...

That should have been[tt] LOCK TABLE table1 READ[/tt]

which prevents others writing to the table during the move operation.

 

[snowboardr]

Well the thing is... I am putting items chosen in a catalog into this table, and I want to move the records to another table after they pay for the items. So I know I can't lock the table that would be equal to locking a mall an expecting people to shop... haha. So do I really have to lock the table, If I am only moving certain users records?

www.sitesd.com
ASP WEB DEVELOPMENT

 

[TonyGroves]

You don't have to lock the table if you don't need to.

Locking would only be needed if there is any chance of a change being made to the set of records involved after the insert but before the deletion; for example if the records comprising the "shopping basket" were copied to the new table, then another item added to the basket by a different client, then the basket was deleted, your database would probably end up in an inconsistent state. However, if there is no possibility of something like that happening, you won't need to lock the table.

You might also like to think about your database design. Moving records between different tables with the same structure sounds (on the face of it) like something that should be avoided if possible. If instead you just had a "paid for" field in the table, that might be a better and more reliable solution. However, you of course know more about your application than I do.

 

[snowboardr]

Good point Tony, However I am thinking that most people who use the shopping cart will want to keep orders for statistcal purposes. Thuse I want to move products added to the users shopping cart after they complete their order so the shopping table doesnt get to full and slow things down for other shoppers.

Do you see any other soultion for this?


Jason

www.sitesd.com
ASP WEB DEVELOPMENT

 

[TonyGroves]

That sounds perfectly reasonable. However, big tables are not necessarily slow, as long as they're well indexed.