CODEWITHSUNDEEP
mysql
Andy Smith
Andy Smith

Reputation: 619

MySQL Query needed: I need to delete all data from a single column

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

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

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

Upvotes: 18

Views: 73750

Answers (7)

Ahmed Soliman
Ahmed Soliman

Reputation: 1710

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

UPDATE table_name SET column_name = null

Upvotes: 0

Blagovest Buyukliev
Blagovest Buyukliev

Reputation: 43558

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.

Upvotes: 1

darioo
darioo

Reputation: 47213

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

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

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.

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.

Upvotes: 4

Jawa
Jawa

Reputation: 2332

Just an interpration...

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

Upvotes: 2

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27536

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

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

Upvotes: 45

Niclas Lindqvist
Niclas Lindqvist

Reputation: 1432

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

Otherwise;

UPDATE products_table
SET product_url=null

Upvotes: 3

EthanM
EthanM

Reputation: 33

Assuming ALL entries are to be filled with NULLs:

UPDATE table_name SET product_url = NULL

Upvotes: 1

Related Questions