SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to alter and add a coloum and drop a coloum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mohamed Faisal
Starting Member

44 Posts

Posted - 10/09/2012 :  10:43:24  Show Profile  Reply with Quote
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
52309 Posts

Posted - 10/09/2012 :  21:28:48  Show Profile  Reply with Quote
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/

Go to Top of Page

Mohamed Faisal
Starting Member

44 Posts

Posted - 10/10/2012 :  12:02:57  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/10/2012 :  23:57:28  Show Profile  Reply with Quote
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/

Go to Top of Page

Mohamed Faisal
Starting Member

44 Posts

Posted - 10/12/2012 :  04:11:16  Show Profile  Reply with Quote
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'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/12/2012 :  12:56:52  Show Profile  Reply with Quote
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/

Go to Top of Page

Mohamed Faisal
Starting Member

44 Posts

Posted - 10/12/2012 :  22:44:57  Show Profile  Reply with Quote
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'.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000