MySQL：如何在表中找到重复记录，并更新另一个表？

I have two tables with folowing structure

我有两张桌子,下面有结构

tbl_inv

SKU   VID  UPC
AAA    2   0123
AA2    3   0123
AA3    4   0123
BBB    2   1234

This table include all products, with sku, vid and UPC number.

此表包括所有产品,包括sku,vid和UPC编号。

tbl_images

SKU   VID  IMAGE_HASHNAME
AAA    2   fcd20a60fd5c1b64cee40ac0c019a022

THIS table includes product with images. I want to make update and inserted images on this products without image and UPC code are match.

该表包括带图像的产品。我想在没有图像和UPC代码匹配的情况下对此产品进行更新和插入图像。

Product with sku AAA, AA2 and AA3 are matched, because UPC numbers are duplicated.

与sku AAA,AA2和AA3匹配的产品,因为UPC编号是重复的。

The final result, which must be obtained is:

必须获得的最终结果是:

AA2 assigned value on AAA for IMAGE_HASHNAME AA3 assigned value on AAA for IMAGE_HASHNAME

对于IMAGE_HASHNAME,AA2为AAA分配值,对于IMAGE_HASHNAME,为AAA分配值

This new recort must inserted in tbl_images

这个新的recort必须插入tbl_images中

tbl_images after update

更新后的tbl_images

SKU   VID  IMAGE_HASHNAME
AAA    2   fcd20a60fd5c1b64cee40ac0c019a022
AA2    3   fcd20a60fd5c1b64cee40ac0c019a022
AA3    4   fcd20a60fd5c1b64cee40ac0c019a022




SELECT i.UPC as upc
        FROM tbl_inv i 
        LEFT JOIN tbl_images img ON img.sku = i.sku AND img.vid = i.vid
        WHERE i.UPC != '' 
        AND img.image_hashname IS NOT NULL
        GROUP BY i.upc
        having count(i.upc) > 1

With this query I trying to find matched records in table, but not show records. After this query I want to copy file with PHP, and insert VALUES in table tbl_images. Where I wrong ?

通过此查询,我尝试在表中查找匹配的记录,但不显示记录。在此查询之后,我想用PHP复制文件,并在表tbl_images中插入VALUES。哪里错了?

I apologize for my English .

我为我的英语道歉。

2 个解决方案

#1

to find the matches

找到比赛

SELECT upc FROM tbl_inv
WHERE sku in ( SELECT sku FROM tbl_images)

this query will return the 0123 which is upc of SKU AAA

此查询将返回0123,即SKU AAA的upc

-here assumption made as sku is unique

作为sku的假设是独特的

#2

Try this

select t3.SKU,t3.VID,t4.IMAGE_HASHNAME 
from tbl_inv as t3 left join 
   (selectt1.SKU,t1.VID,t1.UPC,t2.IMAGE_HASHNAME 
          from tbl_inv as t1 right join tbl_images ON t1.SKU=t2.SKU)
             as t4 on t3.UPC=t4.UPC

#1