Delete specific values from column with where condition?

Question

I want to delete specific values/data from one column with the WHERE condition. Putting in another way, I don't want to delete the complete row. Is it possible?

Please include a sample what you have got now. – Patrick Hofman Jan 29 '14 at 09:02 — Patrick Hofman, Jan 29 '14 at 09:02

score 59 · Answer 1 · edited Oct 12 '20 at 07:15

59

UPDATE YourTable SET columnName = null WHERE YourCondition

edited Oct 12 '20 at 07:15

lbragile

7,549
3
27
64

answered Jan 19 '11 at 07:41

BvdVen

2,921
23
33

score 27 · Answer 2 · edited Jul 23 '20 at 18:15

27

UPDATE myTable 
   SET myColumn = NULL 
 WHERE myCondition

edited Jul 23 '20 at 18:15

Mike

14,010
29
101
161

answered Jan 19 '11 at 07:42

JonBaron

413
3
5

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jan 19 '11 at 08:10

score 19 · Answer 3 · answered Jan 19 '11 at 07:42

You don't want to delete if you're wanting to leave the row itself intact. You want to update the row, and change the column value.

The general form for this would be an UPDATE statement:

UPDATE <table name>
SET
    ColumnA = <NULL, or '', or whatever else is suitable for the new value for the column>
WHERE
    ColumnA = <bad value> /* or any other search conditions */

score 6 · Answer 4 · edited Jul 23 '20 at 18:14

6

You can also use REPLACE():

UPDATE Table
   SET Column = REPLACE(Column, 'Test123', 'Test')

edited Jul 23 '20 at 18:14

Mike

14,010
29
101
161

answered Apr 20 '17 at 15:48

Nathan Holmes

105
2
12

score 1 · Answer 5 · edited Jan 10 '21 at 17:52

1

Try this SQL statement:

update Table set Column =( Column - your val )

edited Jan 10 '21 at 17:52

Shankar Ganesh Jayaraman

1,401
1
16
22

answered Jan 10 '21 at 13:48

haytham fathy

11
1

Delete specific values from column with where condition?

5 Answers5

Linked