Delete values in single a row without using DELETE keyword.

Is it possible to delete the values in single a row without using DELETE keyword....If so whats the Query

[B]Question asked by visitor P.PRAVEEN KUMAR[/b]

update 'tablename' set value=null where 'any unique id'

I thought by this we can delete values....

I don't understand the term "delete the values in single a row"...

give us an example

hello every one
i'm new here i want to become an active part of Geeks Talk but i don't know how so i try with sql i find it so hard iwant learn may be my language is not that good to yndeastand the select andd insert statements i don't know why ?
wuld any one can help make me professional in the sql

u can update the fields by null but it will create another problem. the data is removed but the row is still there (a row of null records).

hello,
well yeah the update will change the values to null a sort of delete but when u ll view the table still that row exists so that does not implies that anything got deleted. still a good attempt

If we are not going to use Delete we could just reset the values to Null or Missing. If we wanted to delete the entire ROW than we would have to be a bit more clever.

In this case, something like this should work:

UPDATE myTable Set field1 = "", field2 = "", field3 = "" where ROWID = 123;

I hope this is what you were looking for.

Though what many of you have suggested i.e. to update the values to NULL sounds fine, but the problem will arise while updating the primary key column value, since a NULL value in that case isn't allowed.

What say !!!

[QUOTE=Geek_Guest;13761]Is it possible to delete the values in single a row without using DELETE keyword....If so whats the Query

[B]Question asked by visitor P.PRAVEEN KUMAR[/b][/QUOTE

as per my knowledge it is not possible.u can updatae values by using the update command.

I thought the question mentioning about values not deleting row or any other thing... So, I hope it may be good one...

Creating a table, and inserting everything except that row.
Generally in data warehousing (some tools are not good with delete statement, like teradata). At that in case you have to delete or update huge part of the table, this is better scenerio to do it.
I hope this helped.

Cheers,
Shilpa.

Hi Shilpha,
Good. But, I have one doubt can you clear it. If previous table was refered by some other tables means what will happen. If any wrong, How can we rectify that.

eg :
As per your thought,

old table
Student(Rollno,Name,Address)

new table
StudentDetails(Rollno,Name,Address)

See here other tables refering 'Student' table
1. Marks
2. Sports

[QUOTE=Geek_Guest;13761]Is it possible to delete the values in single a row without using DELETE keyword....If so whats the Query

[B]Question asked by visitor P.PRAVEEN KUMAR[/b][/QUOTE]



Wht u want exactly???

U can use Truncate ...

It deletes all values premanently....

I also want to know abt. it.

It is not possible bcoz if u updating table then data are deleted but still is remaining with null data & if u used Truncate then it delete records for premanently

[QUOTE=anandhimanickam;18326]Wht u want exactly???

U can use Truncate ...

It deletes all values premanently....[/QUOTE]

Truncate command deletes all the data from the table ,not of a single row.

Nice question mikerich135
According to me this technique will work well in case of data warehouse where u generally have no referencial integrity or soft referential integrity (where there is just a subtable maintained for errors and its never enforced).

But if in case we thought abt databases where referencial integrity is a big contraint then another approach can be adding a new column in table which maintains active/inactive.

Cheers,
Shilpa.

hi
these r the basic questions in sql.
for removing the rows there r two options..
delete is a dml command u can rollback the values
truncat is dml command u cant rollback the values

[QUOTE=satish.paluvai;18461]hi
these r the basic questions in sql.
for removing the rows there r two options..
delete is a dml command u can rollback the values
truncat is dml command u cant rollback the values[/QUOTE]

TRUNCATE is not a DML ,it is a DDL command because it changes the structure of the table not only data. and DDL commands can't be rolledback.

TRUNCATE deletes all the records from a table ,so can't be used for deleting a particular record.

You can create another table with the same structure and insert the rows which are satisfying your conditions i.e. Leave all the rows which you want to delete.


E.g. Original table - A new table - B (structure same as a)


insert into b select * from a where <condition(s)>


cheers! umesh

I don't know whats going on here...
Whether can we delete a single row of table or not?
Finalize the answer...

Yes ,we can we delete a single row of table by using [B]delete[/B] command not [B]truncate[/B].

You cannot delete a single row without using the delete command. By using truncate you can delete all the rows in a table. But you can update the specified row by null values without using delete command using the below query. But logically a row will be there.

[B]update 'tablename' set value=null where 'any unique id'[/B]