CODEWITHSUNDEEP
mysqlsqlsql-updatesql-insert
user3253002
user3253002

Reputation: 1671

Update prices in mysql

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:

Upvotes: 1

Views: 1119

Answers (3)

fthiella
fthiella

Reputation: 49049

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311163

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)

Upvotes: 2

Related Questions