sql - Update prices in mysql -

this how 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 new price list, in format (tbl_supplier)

short_description | description    | gross | net v0056             | valve washroom | 9.50  | 7.99 

how can update price list prices? have in common short description column, has new articles. both lists contain on 10,000 articles , exporting excel + vertical search not work.

i tried this, without success:

update      tbl_articles set      gross =      (         select              gross                       tbl_supplier                       tbl_articles.short_description = tbl_supplier.short_description     ) 

shortcomings:

• new products not added in table
• cannot update 2 fields

create unique index on short_description:

create unique index idx_articles_shortdesc on articles(short_description); 

then use insert . . . on duplicate key update:

insert tbl_articles(short_description, description, gross, net)     select s.short_description, s.description, s.gross, s.net     tbl_supplier s     on duplicate key update gross = values(gross), net = values(net); 

you don't specify want update description, not included.

as note. might want investigate changing dimension tables. think better structure have effective date , end date each pricing. allows keep history of price changes. also, keep date of when record created, know when products introduced.