update tbl1 with data from tbl2

[jonathen]

Hi all,

This should be an easy one to figure out, but for some reason I can't get it to work.

I list alot of small products on ebay, and to make my life easier, I am trying to develop a small script for my logging purposes.

Each lot I sell I have a unique reference # for (i.e 123C). When it is listed on ebay, it has a unique ebay ref # (i.e 123456789)

What I am trying to do is combine two csv's into one table. I have ebay.csv which has item_name,ebay_ref columns. I also have my.csv which has which has item_name,my_ref columns. I have put each into it's own table (the ebay one into "ebay" and my own ref into "mine"). Now what I want to do is tell the db to do the following:

UPDATE mine SET mine.ebay_ref = ebay.ebay_ref WHERE ebay.item_name = mine.item_name

But this gives a sql error. I just want the "mine" table to have item name, ebay ref & my ref, and I want this script to update mine.ebay_ref (new column I have added) with the ebay ref # where the item name matches.

What am I doing wrong? This should be simple, but I can't get it to work!

Thanks,

Jonathen

[drag0n]

ebay.ebay_ref
what is that? It should be a variable or do u just want to insert that in? and what error are you getting?

[jonathen]

ebay.ebay_ref is the ebay_ref field on the ebay table. To make it clearer, this is how each table is set:

table "ebay":
id
item_name
ebay_ref
my_ref

table "mine":
id
item_name
my_ref

"item_name" on each table has the same name for each of the products. "id" is simply the int, so nothing to do with what I am trying to achieve.

I am trying to take my_ref from "mine" and update my_ref in "ebay" where item_name in "ebay" matches item_name in "mine".

The error I get when using:
UPDATE ebay SET my_ref = mine.my_ref WHERE ebay.item_name = mine.item_name

is: Unknown table 'mine' in where clause