Link to home
Start Free TrialLog in
Avatar of sypder
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

SKU (primary) Qty Price Weight

Open in new window


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

Open in new window


The text file would be

SKU (primary) Qty
123 12
234 19
456 36

Open in new window


So the new database should be

SKU (primary) Qty Price Weight
123 12 19.99 0.3
234 199.95 1.3
456 36

Open in new window


I hope that makes sense. Or maybe there is not a single query that will nicely do all of this.
Avatar of chaau
chaau
Flag of Australia image

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
Avatar of sypder
sypder

ASKER

Yeah, but the file does not have the Price and Weight columns, so I want to keep that data if it exists.
SOLUTION
Avatar of Dan Craciun
Dan Craciun
Flag of Romania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sypder

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