MySQL Query需要：我需要从300个表中删除单个字段中的所有数据

I have an ecommerce store that I am currently working on and there are approx 300 products which have a field named "product_url"

我有一个我正在处理的电子商务商店,大约有300个产品有一个名为“product_url”的字段

These fields contains an old url that I need to delete altogether.

这些字段包含一个我需要完全删除的旧URL。

How can I create a query that will replace all "product_url" fields with data in them witha null value?

如何创建一个查询,将所有“product_url”字段替换为具有空值的数据?

7 个解决方案

#1

This will set every product_url to NULL which is currently not null.

这会将每个product_url设置为NULL,这当前不为null。

UPDATE table_name
SET product_url = NULL
WHERE product_url is not null;

#2

First of all, if you have 300 tables (one for each product), you cannot write one query that will set product URLs to NULL.

首先,如果您有300个表(每个产品一个),则无法编写一个将产品URL设置为NULL的查询。

You have to write a query for each table (UPDATE table_name SET product_url = NULL as others have already said).

您必须为每个表写一个查询(UPDATE table_name SET product_url = NULL,正如其他人已经说过的那样)。

And if you have 10,000 products one day, you will have 10,000 tables if you continue like this. This will become a maintenance nightmare since you can see now what kind of problems you have with 300 tables.

如果你有一天有10,000件产品,那么如果你继续这样的话你会有10,000张桌子。这将成为维护的噩梦,因为您现在可以看到300个表有什么样的问题。

Your database is denormalised. If your products share the same attributes, then they should be in one table named "Products", and every product should be represented as one row. Only then can you do what you wanted with one query.

您的数据库是非规范化的。如果您的产品共享相同的属性,则它们应位于名为“Products”的一个表中,并且每个产品应表示为一行。只有这样,你才能通过一个查询做你想做的事。

#3

Do you have a table for each product? If so I don't know.

你有每个产品的桌子吗?如果是这样我不知道。

Otherwise;

UPDATE products_table
SET product_url=null

#4

Just an interpration...

只是一个interpration ......

UPDATE table_name SET column_name = NULL WHERE column_name = 'product_url'

#5

Assuming ALL entries are to be filled with NULLs:

假设所有条目都填充NULL:

UPDATE table_name SET product_url = NULL

#6

If you have lots of tables with the same prefix and the same structure, you could do a SHOW TABLES LIKE prefix_%. Then loop through the result set of this query and run separate queries to each table.

如果你有很多具有相同前缀和相同结构的表,你可以做一个SHOW TABLES LIKE prefix_%。然后循环遍历此查询的结果集,并对每个表运行单独的查询。

#7

This will delete all data in that column without deleting the column itself .

这将删除该列中的所有数据而不删除列本身。

    UPDATE `table_name` SET `column_name`=null

#1