Copy form one table to another

[mehnihma]

HI,

I am new to mysql and have two problems.

1. I have two tables that need to have same values from one field , if that values are not same in second table change it to value of first table

Frist table is ps_product and they all need to have same id_product, the value that I need to change or check is id_category_default and if they are not the same put value in ps_product_shop to be same id_category_default as in ps_product

Can that be done for every entry?

1. Can I do something like;
   To check if the value is 5 in one table then if in second table for same id is 0, put 5 there, also if there is no such id in second table create it and add values from frist table? To go ver all id's and check if they exist in second table?

[adam_k]

I believe you are thinking sequential here, and dbs are powerfull only when they process bulk data.
What you should do is join the tables in an update statement with id_product as key and a where that will filter only the records where ps_product_shop <> id_category_default.
It should look like :

update table_name2 
set ps_product_shop = id_category_default
from table_name1 inner join table_name2 on table_name1.id_product = table_name2.id_product 
where ps_product_shop <> id_category_default

This is in MS SQL syntax, so you might need to change it a bit for MySql.
After you've updated the records where ps_product_shop doesn't match the id_category_default, then you should insert the missing records.
Again I would use join:

insert into table_name2 (field1, field2, field3) 
select field1, field2, field3 from table_name1 left join table_name2 
on table_name1.id_product = table_name2.id_product 
and ps_product_shop = id_category_default
where table_name2.id_product is null 

The above will join the 2 tables and then filter out the records that have in both tables same id_product and ps_product_shope = id_category_default.

[mehnihma]

Hi
Thanks but now I am little bit confused, I hve tried but not made it work.

I have also tried something like this:

INSERT INTO ps_product_shop (id_category_default)
SELECT Distinct id_category_default
FROM ps_product
WHERE id_product BETWEEN 1 AND 6226

And have no luck
Any Ideas?

[adam_k]

Did you get an error?
What do you mean no luck?
Did ps_producy_shop contain any of the id_category_default you were trying to insert?