This is how my articles table looks like: (tbl_articles)
ID | SHORT_DESCRIPTION | DESCRIPTION | GROSS | NET
1 | v00556 | valve washroom | 9.00 | 7.49
etc.
My supplier provided me a new price list, in this format (tbl_supplier)
SHORT_DESCRIPTION | DESCRIPTION | GROSS | NET
v0056 | valve washroom | 9.50 | 7.99
How can I update my price list with his prices? We have in common the short description column, but he has also new articles. Both lists contain over 10,000 articles and exporting to excel + vertical search does not work.
I tried this, but without success:
UPDATE
tbl_articles
SET
Gross =
(
SELECT
Gross
FROM
tbl_supplier
WHERE
tbl_articles.SHORT_DESCRIPTION = tbl_supplier.SHORT_DESCRIPTION
)
Shortcomings:
- New products are not added in my table
- Cannot update 2 fields
Create a unique index on
short_description
:Then use
insert . . . on duplicate key update
:You don't specify that you want to update the description, so that is not included.
As a note. You might want to investigate slowly changing dimension tables. I think a better structure would have an effective date and end date for each pricing. This allows you to keep a history of the price changes. Also, I would keep a date of when the record was created, so I know when products were introduced.