来自不同表的SQL更新表值

I have 2 tables xcart_products which has productid , meta_description and many other fields . The 2nd table is xcart_extra_field_values which has ( productid fieldid and value )

我有2个表xcart_products，它们有productid，meta_description和许多其他字段。第二个表是xcart_extra_field_values，它有（productid fieldid和value）

I need to copy the value of fieldID = 1 of xcart_extra_field_values

我需要复制xcart_extra_field_values的fieldID = 1的值

into

成

the meta_description column of the xcart_products table where the Productid are same.

xcart_products表的meta_description列，其中Productid是相同的。

UPDATE `xcart_products` SET meta_description = ( SELECT value FROM
      xcart_extra_field_values WHERE fieldid = 1 AND
      xcart_extra_field_values.productid = xcart_products.productid ) 
WHERE 
    xcart_extra_field_values.productid = xcart_products.productid ;

I wrote the above SQL but i am getting an error

我写了上面的SQL，但我收到了一个错误

#1054 - Unknown column 'xcart_extra_field_values.productid' in 'where clause'

3 个解决方案

#1

I hope this will work for you:

我希望这对你有用：

UPDATE `xcart_products`, `xcart_extra_field_values` SET xcart_products.meta_description = xcart_extra_field_values.value 
WHERE
xcart_extra_field_values.fieldid = 1
AND
xcart_products.productid = xcart_extra_field_values.productid

#2

You need to the second table name to your query, because it is used in the query, even though you are not changing any data in it.

您需要查询第二个表名，因为它在查询中使用，即使您没有更改其中的任何数据。

UPDATE xcart_products, xcart_extra_field_values

更新xcart_products，xcart_extra_field_values

#3

You could use the multiple-table UPDATE syntax to join the tables instead:

您可以使用多表UPDATE语法来连接表：

UPDATE xcart_products JOIN xcart_extra_field_values USING (productid)
SET    xcart_products.meta_description = xcart_extra_field_values.value
WHERE  xcart_extra_field_values.fieldid = 1;

#1