SQL Query

[Django x2]

Chaps

I have a table with MAC Addresses in, loads in fact.

I need to clear the table completely, but not the columns, just the data in the columns. I can't write 'NULL' to the table as it's protected from that and I don't really want to change it.

Instead, I've used a wildcard query to remove MAC addresses.

Let's say, I have MACs that start with 9C, EC and 08.

delete from table where macaddress like '9C:%'

will remove any MAC address entry that starts with 9C - fine

But, how do I make the query so that I can easily say, delete from table where macaddress like 9C, EC, 08, etc, etc, etc?

Thanks

 

[john_s]

I don't think you can do anything along the lines of

IN(x, y, z)

with LIKE, so the best that immediately comes to mind is:

DELETE FROM table
WHERE macaddress LIKE '9c:%'
OR macaddress LIKE 'EC:%'
OR macaddress LIKE '08:%'

and so on

 

[Django x2]

Cool, that's what I thought, thanks

 

[planty]

Also, if you want to clear the table completely you can just do:

DELETE FROM table;

 

[touch]

Also, if you want to clear the table completely you can just do:

DELETE FROM table;

Yep, doing this wont delete any columns, just the data rows.

DROP table deletes whole table (including columns)
DELETE FROM table deletes data from table but the empty table is still there with all the columns, relationships and other settings.

 

[Weazle]

Also, if you want to clear the table completely you can just do:

DELETE FROM table;

If you're using SQL Server then running the following query is quicker than DELETE FROM to remove all records from a table:
TRUNCATE TABLE <table_name>;

 

[Django x2]

If I do DELETE FROM mytablename I get "Invalid Object Name mytablename"

 

[touch]

Are you looking in the right database?
try:

USE databasename
DELETE FROM tablename

 

[blueboy2001]

You might need to specify the table owner or schema depending on the database structure.

I'd use TRUNCATE TABLE owner.tablename

eg. dbo.tablename

The difference between delete from and truncate is that deletes are fully logged in the transaction log and fires any triggers, and truncate doesn't, so it's faster.

 

[SimonCHere]

If I do DELETE FROM mytablename I get "Invalid Object Name mytablename"

he didn't mean write
DELETE FROM mytablename

you have to change "mytablename" to the name of your table!
In your example above it would be

DELETE FROM macaddress

 

[skyripper]

Do you want to delete those Rows? Because it sounds like you actually want to blank the MacAddress field. Which is different.

DELETE FROM whateverthetablenameis WHERE macaddress LIKE 'aa%' OR macaddress LIKE 'bb%' or macaddress LIKE 'cc%';

versus

UPDATE whateverthetablenameis SET macaddress=' ' WHERE macaddress LIKE 'aa%' OR macaddress LIKE 'bb%' or macaddress LIKE 'cc%';



Edit: of course, I may just be misreading it...