sypder
asked on
MySQL query to UPDATE, INSERT or DELETE
I know of a brute force way to do this, but it struck me that there might be an elegant way in MySQL to get this done. I have a database with 4 columns
Then once a day I receive a text fill with the update SKU and Qty list. There may be new SKUs are SKUs that no longer exist. So there are three conditions:
1. If SKU already exists in DB, just update Qty
2. If SKU does not exist in the DB, add it and the Qty with Price and Weight blank
3. If SKU exists but is not in text file, delete the row
Here is an example:
The text file would be
So the new database should be
I hope that makes sense. Or maybe there is not a single query that will nicely do all of this.
SKU (primary) Qty Price Weight
Then once a day I receive a text fill with the update SKU and Qty list. There may be new SKUs are SKUs that no longer exist. So there are three conditions:
1. If SKU already exists in DB, just update Qty
2. If SKU does not exist in the DB, add it and the Qty with Price and Weight blank
3. If SKU exists but is not in text file, delete the row
Here is an example:
SKU (primary) Qty Price Weight
123 12 19.99 0.3
234 24 9.95 1.3
345 48 89.98 3.5
The text file would be
SKU (primary) Qty
123 12
234 19
456 36
So the new database should be
SKU (primary) Qty Price Weight
123 12 19.99 0.3
234 199.95 1.3
456 36
I hope that makes sense. Or maybe there is not a single query that will nicely do all of this.
I think the easiest thing you can do is to delete all data from your table and replace it with the data from the file. You may need to consider creating a backup before that
ASKER
Yeah, but the file does not have the Price and Weight columns, so I want to keep that data if it exists.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Everyone, thanks. This looks perfect. I find it important to do these simple tasks well and this looks nice.
Simple tasks done well are the foundation of all civilizations. Thanks for the points and thanks for using EE, ~Ray