February 8, 2015 at 10:29 am
Hello
If I want to change column or add something to it I should only use modify ?
Example
alter table tablename
modify column columname type;
is there other command do the same thing ? other than modify ?
February 8, 2015 at 10:37 am
Quick note, the modify method only applies to the XML data type, to update a value in a table use the UPDATE statement The following example updates the value of COLUMN_X in TABLE_A to the value of 5 where it is missing (NULL).
😎
UPDATE TABLE_A
SET COLUMN_X = 5
WHERE COLUMN_X IS NULL;
February 8, 2015 at 10:40 am
Thanks for reply, but what I wanted sorry I explained bad.
For example I have column
courseno int PRIMARY KEY
I want to change it to
courseno int
to change datatype, or change name, whatever with the structure it is build with I want to play with it.
Currently I use MODIFY and it did work! what other command will work ?
February 8, 2015 at 11:11 am
In this case you have to use the ALTER statement, here is a quick example
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* Drop the table if it already exists */
IF OBJECT_ID(N'dbo.TBL_CHANGE') IS NOT NULL DROP TABLE dbo.TBL_CHANGE;
/* Create a sample table */
CREATE TABLE dbo.TBL_CHANGE
(
COURSENO INT NOT NULL CONSTRAINT PK_DBO_TBL_CHANGE_COURSENO PRIMARY KEY
,MYVAL INT NULL
);
/* Add some sample data */
INSERT INTO dbo.TBL_CHANGE(COURSENO,MYVAL)
SELECT 1,100 UNION ALL
SELECT 2,102 UNION ALL
SELECT 3,103 UNION ALL
SELECT 4,104 UNION ALL
SELECT 5,105
;
/* Remove the primary key constraint */
ALTER TABLE dbo.TBL_CHANGE DROP CONSTRAINT PK_DBO_TBL_CHANGE_COURSENO;
/* Change the MYVAL column from INT to TINYINT */
ALTER TABLE dbo.TBL_CHANGE ALTER COLUMN MYVAL TINYINT NOT NULL;
SELECT
T.COURSENO
,T.MYVAL
FROM dbo.TBL_CHANGE T;
February 9, 2015 at 3:26 am
It did not work, maybe becuase am using different MYSQL WorkBranch ?
February 9, 2015 at 8:30 am
hanialbarni (2/9/2015)
It did not work, maybe becuase am using different MYSQL WorkBranch ?
Maybe because this is a SQL Server forum and you weren't polite enough to mention that you're using MySQL. You might get help here, but you'll get better help in a MySQL forum because syntax and many other things change from one RDBMS to another.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply