how to alter and add a coloum and drop a coloum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to alter and add a coloum and drop a coloum

Author

Topic

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2012-10-09 : 10:43:24

Assuming that the CaseTypeCode table has been populated with all possible
case types and each case type has been assigned a type code, replace the
column caseType in the Case table with a new column typeCode. Add the
correct type into the existing rows in the Case table to refer to the
corresponding typeCode given the caseType of the case.

The Table for CaseTypeCode is:

CREATE TABLE CaseTypeCodeTable
( typeCode CHAR(3) NOT NULL,
caseType Char(255) NOT NULL,
CONSTRAINT CaseTypeCodeTablePK PRIMARY KEY(typeCode),
CHECK(typeCode like '[A-Z][0-9][0-9]')
);
And my case table is:

CREATE TABLE CaseTable
(
referenceNum int NOT NULL IDENTITY(100000,1),
startDate DATETIME NOT NULL,
endDate DATETIME NULL,
caseDetail Char(255) NOT NULL,
caseType Char(255) NOT NULL DEFAULT'copyright and trademark',
clientID Char(5) NOT NULL,
CONSTRAINT CaseTablePK PRIMARY KEY(referenceNum),
CONSTRAINT ClientTableFK FOREIGN KEY(clientID)
REFERENCES ClientTable(clientID),
CONSTRAINT caseTypeValues CHECK(caseType LIKE 'intellectual property enforcement' OR
caseType LIKE'copyright and trademark' OR caseType LIKE'patent and industrial design' OR
caseType LIKE'trade secret' OR caseType LIKE 'risk management' OR
caseType LIKE'litigation'),
CONSTRAINT clientIDValues CHECK(clientID LIKE '[A-Z][0-9][0-9][0-9][A-Z]')
);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 21:28:48

its just a matter of straightforward UPDATE based on caseType column. can we see what you tried so far?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2012-10-10 : 12:02:57

Hi visakh16,

my code:

ALTER TABLE CaseTable
ADD COLUMN typeCode CHAR(3) NOT NULL,
ADD CONSTRAINT CaseTableFK FOREIGN KEY (typeCode) REFERENCES CaseTypeCodeTable (typeCode)

Not sure how to drop the constraint of the caseType??

ALTER TABLE CaseTable DROP COLUMN caseType;

Not sure how to update the column (typeCode) in the CaseTAble to the column(typeCode) of the CaseTypeCodeTable??

Describe CaseTable;

and my other code i tried is but getting syntax error:

ALTER TABLE CaseTable
ADD typeCode char(3) NOT NULL,
UPDATE CaseTable (not sure how to update the column of typeCode to the table of CaseTypeCodeTable)
SET typeCode,
ADD CaseTableFK FOREIGN KEY(typeCode) REFERENCES CaseTypeCodeTable (typeCode),
ALTER TABLE CaseTable
DROP CONSTRAINT caseTypeValues,
ALTER TABLE CaseTable
ALTER caseType DROP DEFAULT,
drop column caseType ;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-10 : 23:57:28

the update will be like


UPDATE ct
SET ct.typeCode = ctc.typeCode
FROM CaseTable ct
INNER JOIN CaseTypeCodeTable ctc
ON ctc.caseType = ct.caseType



 and all the other statements look fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2012-10-12 : 04:11:16

Hi visakh16,

i have doe the cording and haveing error. My code is:

ALTER TABLE CaseTable
ADD typeCode char(3) NOT NULL,
UPDATE TABLE CaseTable
SET ct.typeCode = ctc.typeCode
FROM CaseTable ct
INNER JOIN CaseTypeCodeTable ctc
ON ctc.caseType = ct.caseType,
SET typeCode
ADD CaseTableFK FOREIGN KEY(typeCode) REFERENCES CaseTypeCodeTable (typeCode),
ALTER TABLE CaseTable
DROP CONSTRAINT caseTypeValues,
ALTER TABLE CaseTable
drop column caseType ;

And error msg i am getting is:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'UPDATE'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'ALTER'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-12 : 12:56:52

just copy paste update as suggested. you're again using wrong syntax!


ALTER TABLE CaseTable 
ADD typeCode char(3) NOT NULL

ALTER TABLE CaseTable ADD CONSTRAINT  CaseTableFK FOREIGN KEY(typeCode) REFERENCES CaseTypeCodeTable (typeCode) 


UPDATE ct
SET ct.typeCode = ctc.typeCode
FROM CaseTable ct
INNER JOIN CaseTypeCodeTable ctc
ON ctc.caseType = ct.caseType

ALTER TABLE CaseTable
DROP CONSTRAINT caseTypeValues

ALTER TABLE CaseTable
drop column caseType

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2012-10-12 : 22:44:57

Hi visakh16,

I tried what you said. But get an error.

Code:

ALTER TABLE CaseTable
ADD typeCode char(3) NOT NULL

ALTER TABLE CaseTable ADD CONSTRAINT CaseTableFK FOREIGN KEY(typeCode) REFERENCES CaseTypeCodeTable (typeCode),
CHECK(typeCode like '[A-Z][0-9][0-9]')
UPDATE ct
SET ct.typeCode = ctc.typeCode
FROM CaseTable ct
INNER JOIN CaseTypeCodeTable ctc
ON ctc.caseType = ct.caseType

ALTER TABLE CaseTable
DROP CONSTRAINT caseTypeValues

ALTER TABLE CaseTable
drop column caseType

Error:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'typeCode'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources