March 24, 2010 at 11:51 pm
I have created 2 tables.
person <------------ profession
______ one to many _________
p_id(pk) prof_id(pk)
first_name profession
profession
prof_id(fk)
CREATE TABLE profession (
prof_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
profession VARCHAR(50) NOT NULL
)
CREATE TABLE person (
p_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
)
CREATE TABLE person (
p_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAR(50),
profession VARCHAR(50) NOT NULL,
prof_id INT NOT NULL,
CONSTRAINT profession_prof_id2_fk
FOREIGN KEY (prof_id)
REFERENCES profession(prof_id)
)
Say i have the profession table like this:
prof_id profession
1 Writer
2 Poet
3 Programmer
4 System Admin
And the person table like this:
p_id first_name profession prof_id
1 James Programmer
2 Michael Writer
3 Bellamy Writer
4 Carl Programmer
But how can i now populate the prof_id column in the person table with the value of prof_id for the respective value of their profession ?
And the person table should look like this:
p_id first_name prof_id
1 James 3
2 Michael 1
3 Bellamy 1
4 Carl 3
March 25, 2010 at 2:56 am
Hi,
You can make a function which gets the profession name paramater and out an int (the porf id) and then make just un update in the person table,
Or... without a function just make un update with a join between person.profession name and prof.profession name
If I didn't understand what the problem is, pls be more explicit.
Luck!
Wish you good ideas! 🙂
Andreea
March 25, 2010 at 7:18 am
Here's a script that will do what you want to do. In my test code I had to turn off your FK check to insert the sample data, you may or may not have to do this in your environment depending on what the data actually looks like and your DDL.
But here you go...
--Do this someplace Safe for testing
USE [tempdb]
--Crete your tables
CREATE TABLE profession (
prof_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
profession VARCHAR(50) NOT NULL
)
GO
CREATE TABLE person (
p_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAR(50),
profession VARCHAR(50) NOT NULL,
prof_id INT NOT NULL,
CONSTRAINT profession_prof_id2_fk
FOREIGN KEY (prof_id)
REFERENCES profession(prof_id)
)
GO
--INSERT Test data TO Professions table
INSERT INTO [profession]
SELECT 'Writer' UNION ALL
SELECT 'Poet' UNION ALL
SELECT 'Programmer' UNION ALL
SELECT 'System Admin' ;
--Turn off the FK check to add sample data
ALTER TABLE person NOCHECK CONSTRAINT profession_prof_id2_fk;
--add Sample Data
INSERT INTO person
SELECT 'James', 'Programmer', 0 UNION ALL
SELECT 'Michael','Writer', 0 UNION ALL
SELECT 'Bellamy','Writer', 0 UNION ALL
SELECT 'Carl','Programmer', 0;
--Update the sample data
UPDATE p
SET p.[prof_id] = pf.[prof_id]
FROM Person p
INNER JOIN [profession] pf
ON p.[profession] = pf.[profession];
--Turn the FK check back on
ALTER TABLE person CHECK CONSTRAINT profession_prof_id2_fk;
--Show results
SELECT *
FROM [person];
--Cleanup
DROP TABLE [person];
DROP TABLE [profession];
March 25, 2010 at 10:03 am
@SSCrazy
Thank You so much Sir.
It worked perfectly and cleared my doubts.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply