RE: Delete Syntax

[allyne]

Hi everyone,

I'm using SQL 7.0 and need to write a stored procedure that will delete all the information from specific fields in a table. For example, I tried

DELETE
profitability, Unprofitable, MOU%
FROM tblFinalOneLine
(This doesnt' work)

I also Tried

DELETE tblFinalOneLine FROM(SELECT
profitability, Unprofitable, MOU% FROM tblFinalOneLine) AS [60] WHERE
[60].profitability = tblFinalOneLine.profitability and
[60].Unprofitable = tblFinalOneLine.Unprofitable and
[60].MOU% = tblFinalOneLine.MOU%

(This also didn't work. This deletes all the information out of tblFinalOneLine and then selects the fields.)

Can someone tell me what I'm doing wrong?

Thanks for all your help
Cathy

 

[tlbroadbent]

Delete is used to remove records or rows of data. If you want to remove data from individual columns you'll need to update the columns.

Setting columns to NULL
Update tblFinalOneLine
Set profitability=null, Unprofitable=null, MOU%=null

If your intent is to remove the columns from the table, you'll use the Alter table command.

ALTER TABLE tblFinalOneLine
DROP COLUMN profitability, Unprofitable, MOU%

Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[allyne]

Thanks! It worked!
Cathy

 

[tlbroadbent]

Which one worked? Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[allyne]

Update tblFinalOneLine
Set profitability=null, Unprofitable=null, MOU%=null

Thanks!