mysql select from where insert query

[mbk]

Hi,

 

I have two tables, fulldata and suppliers, fulldata contains all data available from my vendor, suppliers contains the data that my suppliers stock.  within the fulldata table is a field called image, which I want to include in the suppliers table, with the common field between the two being m_prod_id.

 

I have the following code, but am stuck as to how I can assign the relevant m_prod_id the correct image value, also some m_prod_id's will not have an image value.

 

select fulldata.image from fulldata, suppliers where fulldata.m_prod_id = suppliers.m_prod_id

 

Can someone help pls?

[mbk]

Can anyone help pls?

[Adam]

but am stuck as to how I can assign the relevant m_prod_id the correct image value

 

Could you elaborate on this a little more?

[mbk]

Thanks for the response

 

OK, in my table fulldata, m_prod_id contains a part number, then the image field contains a link to the image of that relevant part.  My Supplier table contains an m_prod_id field, but no link to the image in the image field.  What I want is to use the m_prod_id in the supplier table to query the fulldata table, if it exists in the fulldata table then copy the image field from the fulldata table to the supplier table in the image field, if it doesnt exist then insert NULL and continue.

 

Have I made any sense? 

[Adam]

A query like this should do the trick:

 

update suppliers
set suppliers.image = (
    select fulldata.image from fulldata
    where fulldata.m_prod_id = suppliers.m_prod_id
);

 

Why would you need to store the image twice though? That's twice the work to insert/update/delete just 1 image.

[mbk]

Hi,

 

Thanks for the response.

 

Reason behind is that I get a feed from a supplier of my product data which is in the fulldata table.  I then get feeds from my suppliers of goods they stock, however this isnt as in depth as the product data, so I am basically cross referencing the data from one table to the other, so I end up with a complete list of goods my suppliers will supply me and where applicable it will contain in depth product data.

 

Is there an easier way to do it?