Help with a DELETE function and dropping columns
I am writing a delete function for my jsp page, and I am running into some trouble because I want the user to be able to delete only certain columns from a table, but leave the other columns. My jsp page has 4 tables, and the user can delete from any one of those (note the "if statements")....I know what I have here is incorrect, but it is my attempt. Any help is appreciated, also if it would be better to handle some of this in the servlet, please let me know as well.
BEGIN
-- If a deletion is checked on the Lost/Found table.
IF pLfEdit IS NOT NULL
THEN
ALTER TABLE BSSS
DROP COLUMN LOST_FOUND
DROP COLUMN LOST_LOC
WHERE EDIT_ID = pLfEditDel;
ELSE
RAISE NO_DATA_FOUND;
END IF;
-- If a deletion is checked on the Returned Check 开发者_如何转开发table.
IF pRcEdit IS NOT NULL
THEN
ALTER TABLE BSSS
DROP COLUMN RETURN_CHECK
DROP COLUMN RETURN_LOC
WHERE EDIT_ID = pRcEditDel;
ELSE
RAISE NO_DATA_FOUND;
END IF;
-- If a deletion is checked on the Sponsor table.
IF pSpnEdit IS NOT NULL
THEN
ALTER TABLE BSSS
DROP COLUMN SPONSOR
DROP COLUMN SPON_LOC
WHERE EDIT_ID = pSpnEditDel;
ELSE
RAISE NO_DATA_FOUND;
END IF;
-- If a deletion is checked on the Comments table.
IF pComEdit IS NOT NULL
THEN
ALTER TABLE BSSS
DROP COLUMN COMMENTS
WHERE EDIT_ID = pComEditDel;
ELSE
RAISE NO_DATA_FOUND;
END IF;
COMMIT;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RETURN SQLCODE;
END DEL_BS_INFO;
You are mixing the concepts of DML and DDL. DDL (e.g. ALTER TABLE) is for system change and maintenance, usually done by a DBA; DML (e.g. SELECT, INSERT, DELETE, COMMIT) is for transactions which are done by the application.
I think you don't want to DROP the columns from the table - but you want to clear the values from individual columns in some rows. What you want to do is set them to NULL, I think. For example:
IF pLfEdit IS NOT NULL
THEN
UPDATE BSSS
SET LOST_FOUND = NULL,
LOST_LOC = NULL
WHERE EDIT_ID = pLfEditDel;
ELSE
RAISE NO_DATA_FOUND;
END IF;
精彩评论