| Author |
 Topic  |
|
|
Mohamed Faisal
Starting Member
26 Posts |
 Posted - 10/09/2012 : 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]')
);
|
Edited by - Mohamed Faisal on 10/09/2012 12:59:48
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 10/09/2012 : 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
Starting Member
26 Posts |
Posted - 10/10/2012 : 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 ; |
Edited by - Mohamed Faisal on 10/10/2012 13:40:50 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 10/10/2012 : 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
Starting Member
26 Posts |
Posted - 10/12/2012 : 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
India
47023 Posts |
Posted - 10/12/2012 : 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
Starting Member
26 Posts |
Posted - 10/12/2012 : 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'. |
|
|
| |
Topic |
|
how to alter and add a coloum and drop a coloum
