Update prices in mysql

Question

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

Answer 1

Create a unique index on short_description:

create unique index idx_articles_shortdesc on articles(short_description);

Then use insert . . . on duplicate key update:

insert into tbl_articles(SHORT_DESCRIPTION, DESCRIPTION, GROSS, NET)
    select s.SHORT_DESCRIPTION, s.DESCRIPTION, s.GROSS, s.NET
    from tbl_supplier s
    on duplicate key update gross = values(gross), net = values(net);

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.

Answer 2

Assuming the SHORT_DESCRIPTION field is an actual primary/unique in the table, the easiest thing to do would be to have an insert statement with an on duplicate key update clause:

INSERT INTO tbl_articles 
(short_description, description, gross, net) -- Assumes ID is autogenerated
SELECT short_description, description, gross, net
FROM   tbl_supplier
ON DUPLICATE KEY UPDATE gross = VALUES(gross), net = VALUES(net)

Answer 3

You can use an UPDATE query with a join:

UPDATE
  tbl_articles a INNER JOIN tbl_supplier s
  ON a.short_description = s.short_description
SET
  a.gross=s.gross,
  a.net=s.net