Member Avatar for mls880

Hi Guys

Firstly:

Is it possible to import two columns from an excel sheet into two new columns in a mysql database?

I would like to import new product codes(prodcode2) and pricing(prodsupplyprice2) into these two columns and then set up a query to do the folowing...

I need to compare original product codes column(prodcode) with the new imported product code column(prodcode2) and if the same code is present anywhere in the two columns, update the products price(prodsupplyprice) with the newly imported price(prodsupplyprice2).

This becomes challange because most of the time the product codes are not lined up next to each other and are unordered.

I figure you need to use a SELECT statement and MATCH statement to select the matching codes and maybe reorder (prodcode2) to line up with (prodcode)

From there you just use the UPDATE statement to change the prices.

How I see it:

SELECT * FROM products WHERE MATCH (prodcode) AGAINST (prodcode2);

How? This doenst work, I have tried combinations and sometimes get a FULLTEXT error..?

Then, need to somehow reorder corrisponding codes next to each other... How?

Then

UPDATE `products` SET `prodsupplytprice` = (`prodsupplyprice2`) WHERE `prodsupplyprice2` > 0 ;

Any help on this would be greatly appreciated and could save me HOURS updating prices... or if there is a better way of doing this please let me know :)

Many Thanks
Marc

  • 2 Contributors
  • 1 Reply
  • 97 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by fwdean
Member Avatar for fwdean

I think there is a much easier way. Before I go on, I'll read back to you what I think you want. You have a two column list. You have a new two column list. You'd like the first lists' price column to be updated if there is a matching code in the codes column in the second list. If the above is correct, here's what I would do.

1. Create a mysql table with fields for code and price with a primary key on code. This will make sure you only have one row of data for each unique code. Also helps #4 below work.

2. Export your new columns from excel to a .csv file.

3. Use your favorite language to read that .csv file one line at a time.

4. Use the following type of statement on the data you just read.

INSERT INTO tablename (code, price) VALUES ('codevalue','pricevalue') ON DUPLICATE KEY UPDATE price = 'pricevalue'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.