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 first published in June 2005