Q
Problem solve Get help with specific problems with your technologies, process and projects.

Move a row from one table to another

I'm trying to figure out how to write an SQL query that deletes from one table and inserts into another. I have...

tried writing

DELETE FROM dbo.MojoBeers
WHERE (productID = @productID)
UNION
INSERT INTO dbo.MojoShoppingCart
(productID) VALUES (@productID)

Every time I get a parse error, and I'm not sure why. If you could help me out with this I would be grateful.

Well, the answer to this is pretty simple. You can't UNION a DELETE and an INSERT. You can only UNION SELECTs. You'll just have to run the DELETE and INSERT as separate statements. Depending on your database system, you might be able to submit both of them in one call to the database if they're separated by a semicolon.

Also, it's more common to do the INSERT first. Typically, you'll want to copy some of the fields from one table to the other. So you would do something like:

insert
  into table2
     ( pkey
     , field1
     , field2
     , fieldN )
select pk
     , col1
     , 'foo2'
     , colN      
  from table1
 where pk = 937

Often, you would run these queries from some scripting language like Coldfusion, ASP, or PHP. It might be wise to check the status code of the INSERT to make sure it was successful before continuing with the DELETE.

delete
  from table1  
 where pk = 937

Yes, there's extra overhead in making two calls to the database instead of one, but it's minimal.

This was last published in June 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close