how to delete a value from a column in SQL

Question

How can I delete only the field of a column without deleting the entire row? I have a table that owns the fields: ID, Name, Mail, Phone, Company and Would like to delete only the email of a person without deleting all the fields.

If I use:

DELETE FROM TEST_TABLE WHERE MAIL = 'myemail@gmail.com'

that way will delete everything and I want to delete just the email

Don't update to an empty string, use NULL for non-existing values. — jarlh, Apr 25 '22 at 17:45
I'm using teradata, I just wanted to understand how it works because it was with the thought in DELETE and not in UPDATE, thanks for the help @jarlh — FireWolfBR, Apr 25 '22 at 18:19

score 1 · Answer 1 · answered Apr 25 '22 at 17:50

1

you can use this

Update myTable set MyColumn = NULL where Field = Condition.

References

1- How do I set a column value to NULL in SQL Server Management Studio?

2- UPDATE, DELETE, and INSERT Statements in SQL

answered Apr 25 '22 at 17:50

hani

11
1

Note that OP is using Teradata or perhaps MySQL. So MS SQL Server links can be a bit misleading. – jarlh Apr 25 '22 at 18:09
Perfect, solved my problems, thank u very much for your help :D – FireWolfBR Apr 25 '22 at 18:10

score 0 · Answer 2 · answered Apr 25 '22 at 17:52

0

try

UPDATE
  TEST_TABLE 
SET
  MAIL = ''
WHERE
  id = your_id

or

if you want delete the field

ALTER TABLE TEST_TABLE 
DROP COLUMN MAIL;

answered Apr 25 '22 at 17:52

Rafael Gonçalves

76
1
6

Don't update to an empty string, use NULL for non-existing values. – jarlh Apr 25 '22 at 17:52
@jarlh, in MySql you can't set field as NULL using a NULL value. In this case, use 'NULL' (string), or empyt value instead – Rafael Gonçalves Apr 25 '22 at 17:57
https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=a2501b2c15ba508bf0f26ba195362e52 – jarlh Apr 25 '22 at 18:05
in pure sql it works fine, but in programming languages, ORMs parsing doesn't allow NULL values like that – Rafael Gonçalves Apr 25 '22 at 18:13
OP asked for an SQL answer. (BTW, OP has now specified that Teradata, not MySQL, is used.) – jarlh Apr 25 '22 at 18:28
@jarlh now that i saw that he is using teradata. in this case I don't know how NULL works. tks – Rafael Gonçalves Apr 25 '22 at 18:29

score 0 · Answer 3 · answered Apr 25 '22 at 20:45

It is good practice to update the field with a NULL instead of leaving it blank, this indicates that there is a missing value and will later allow you to do queries where something is NOT NULL, which will give better results if it isn't returning bad data. Remember, garbage in, garbage out.

UPDATE TEST_TABLE SET MAIL = NULL WHERE MAIL = 'myemail@gmail.com'

how to delete a value from a column in SQL

3 Answers3