MySQL copy selected fields from one row in table 1 to table 2 with different columns and then delete the entry from table 1?

Question

I have table named 'Fruit' with 5 columns ('fruit_id', 'name', 'color', 'size', 'weight') where 'fruit_id' is AUTOINCREMENTED. In this table I have 3 entries:

1, Apple, Red, Medium, 1.0
2, Banana, Yellow, Small, 0.5
3, Pear, Green, Large, 2.0

Of which, I only want to copy the following selected fields from row 2 (Banana, Yellow, 0.5) into another table named 'Shipment' which has 5 columns ('ship_id', 'name', 'color', 'price', 'weight').

I don't want to enforce any foreign key relationship between the two tables as I will be deleting the entry from 'Fruits' after the selected fields have been copied into 'Shipment'. Here's what I have so far:

INSERT INTO "Shipment" ("name", "color", "price", "weight") SELECT "name", "color", "weight"  FROM "Fruit";

DELETE FROM "Fruit" WHERE "fruit_id" = 2;

But it's giving errors.

Also is there a more elegant method to combine the INSERT and DELETE together into one statement?

Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — Raymond Nijland, Aug 19 '18 at 00:45
Database, table and column names ***can't*** be single qouted in MySQL unless sql_mode ANSI_QUOTES is enabled on the MySQL server. — Raymond Nijland, Aug 19 '18 at 00:47
I'll edit it to double quotes then. But I just wanted help on the logical method of things. — Jae Bin, Aug 19 '18 at 00:48
"I'll edit it to double quotes then" Just ***remove*** the single or double qoutes by database, table and column names much better option — Raymond Nijland, Aug 19 '18 at 00:49
If it was me, I'd get rid of the fruit table altogether, and just store everything in shipment — Strawberry, Aug 19 '18 at 07:33

MySQL copy selected fields from one row in table 1 to table 2 with different columns and then delete the entry from table 1?

0 Answers0