CODEWITHSUNDEEP
sqlsql-server
ksk
ksk

Reputation: 307

Copy primary key column data into other column of same table

Can anyone help me to copy primary key column data into another column of same table?

Example: Student table

ID PK
Name
age
IID

I want to check if IID column is null, then I want it to update with primary key column value of the same table, and looking for some generic way to do this as we have this kind of requirement for multiple tables.

We have other table like this

Dep 

DID PK
DNAME
IID

I'm trying but no table to find appropriate way to do this.

Upvotes: 0

Views: 1398

Answers (2)

Thiyagu
Thiyagu

Reputation: 1330

You can add Foreign Key References to the Same Table:

CREATE TABLE  DEPT (
ID INT Primary Key,
Name VARCHAR(50),
age INT ,
IID INT FOREIGN KEY REFERENCES DEPT(ID))

By using UPDATE Statement:

UPDATE DEPT
SET IID=ID
WHERE IID IS NULL

Upvotes: 0

Karan
Karan

Reputation: 12619

Create a stored procedure like the one shown below and pass table name, primary key column name & copy to column name to it. It will create query and execute.

CREATE PROCEDURE [dbo].[UpdateColumnValue]
    @tableName as varchar(100),
    @copyFromColumnName as varchar(100),
    @copyToColumnName as varchar(100)
AS
BEGIN

    DECLARE @query as varchar(max);

    SET @query = ' UPDATE ' + @tableName + 
                 ' SET ' + @copyToColumnName + ' = ' + @copyFromColumnName +
                 ' WHERE ' + @copyToColumnName + ' IS NULL';

    EXECUTE(@query);

END

Upvotes: 1

Related Questions