How to update value of a row in database table without changing its previous value or any other columns?

Question

0.00/5 (No votes)

See more:

I have a table which stores Employee Id, Names, Skills, Salary of an employee. I have one more table which has Skill Name and Skill Id and one more mapping table which has two foreign key Employee Id and Skill Id which automatically gets updated when some selects a skill. Now I have created an Update Method in controller which take previous record of the particular employee if the user has updated means selected another new skill the mapping table is adding the records of the all the skill again which is causing an error of primary key constraint because previous skills are already mapped i just want to map the new skill in the mapping table. Thank you

What I have tried:

I have tried fetching both the values previous as well as new+previous and adding the new value to an array and passing it to db context update method but the previous skills are also going in for mapping which i dont want

Posted 22-Feb-23 16:56pm

Shriyansh Dubey

Updated 22-Feb-23 23:37pm

Add a Solution

Comments

PIEBALDconsult 22-Feb-23 23:36pm

Which database system? If SQL Server, maybe try a MERGE statement.

Shriyansh Dubey 23-Feb-23 0:03am

Yes sql server but i am using code first approach is possible to add condition to the context.savechanges to save only those mappings which is unique

PIEBALDconsult 23-Feb-23 8:01am

I wouldn't know, I always do things the right way.

Shriyansh Dubey 23-Feb-23 0:04am

Sorry I just started learning these concepts so maybe my explanation won't be much clear.

Member 15627495 23-Feb-23 0:05am

add a primary key (by AUTO-INCREMENT) to your Mapping table ( instead of overwriting old value ).

it will be fill with a 'insert into Mapping_table(emp_id,skill_id) value (emp_id_value, skill_id_value);' instead of 'update' query.

then :
- add a column to 'Mapping table' [ as primary key auto increment ]
- use 'insert' query

Shriyansh Dubey 23-Feb-23 0:16am

Thanks. Will try these methods and let you know.

Member 15627495 23-Feb-23 0:10am

for the 'error of primary key constraint', your have to mute the constraint from : "1,1" , to "0,n".
"1,1" or "0,1" are blocking if you have more than 1 record.
"0,n" are open for lots of "row"

Shriyansh Dubey 23-Feb-23 0:23am

i forgot to add that the Mapping table already has a composite Primary key with (skillid,employeeid) so i cant add another primary key

Member 15627495 23-Feb-23 0:27am

if you have a composite Primary key like this, you don't need the two other columns because of Datas in. but that is not the point.
add a 'fourth' column as AUTO_increment.

it's a "database Design error" that block your coding.

Shriyansh Dubey 23-Feb-23 0:42am

I added the column but still getting the error
Violation of PRIMARY KEY constraint 'PK__SkillMap__A9381790A4ABDACA'. Cannot insert duplicate key in object 'dbo.SkillMap'. The duplicate key value is (1, 24).

Shriyansh Dubey 23-Feb-23 0:45am

Violation of PRIMARY KEY constraint 'PK__SkillMap__A9381790A4ABDACA'. Cannot insert duplicate key in object 'dbo.SkillMap'. The duplicate key value is (1, 24).

1 is foreign key skill id from skill table and 24 is the foreign key from employee table.
the mapping table already has 1,24 means employee no.24 already has skill no.1.
I just Want to add another skill like 2,24 but during context.Savechanges all the skills present goes to the mapping table which creates the error

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2023-02-22T23:37:00

This is a standard update problem for any use case and there are multiple ways to solve it.
Lets take your example.

SQL

create table Employee 
(
   EmployeeId int primary key,
   Names varchar(100),
   skills varchar(200),
   Salary numeric(10,2)
)

create table Skill 
(
   Skillid int primary key,
   Names varchar(100)
 )

CREATE TABLE EmployeeSkillMapping
( employeeid INT FOREIGN KEY REFERENCES Employee(EmployeeId),
  skillid INT FOREIGN KEY REFERENCES skill(Skillid),
)

Now let's add some dummy data

SQL

INSERT INTO Employee(employeeid, names, salary)
SELECT 1, 'Test1', 100

insert into skill (skillid, names)
SELECT 1, 'C++'
UNION ALL
SELECT 2, 'C#'
UNION ALL
SELECT 3, 'Java'
UNION ALL
SELECT 4, 'ReactJS'
UNION ALL
SELECT 5, 'NodeJS'

insert into EmployeeSkillMapping(employeeid, skillid)
SELECT 1, 1
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 3

The usecase here is Employee Name "Test" have three skills (C++, C# and Java). Now we need to modify Asif by removing Java skill and add new ReactJS skill.

Usually the interface for doing above modifications is on the left side we have all available skills and at the right side we have assigned skills list.

One elegant approach is to use MERGE. Merge command can Insert/Update/Delete in one transaction. You can call an SP/SQL from code passing all assigned skills list to the SP and the SP will do the Job, may be like this.

SQL

MERGE EmployeeSkillMapping AS tgt  
USING (
  VALUES 
        (1, 1),
		(1, 2),
        (1, 4)
) AS src (employeeid, skillid) 
    ON tgt.employeeid = src.employeeid and tgt.skillid = src.skillid
WHEN NOT MATCHED THEN  
    INSERT (employeeid, skillid)  
    VALUES (src.employeeid, src.skillid) 
WHEN NOT MATCHED BY SOURCE THEN
  DELETE

Another approach is to break it into two parts. One is for Insert and another is for deletion. You already have the previous assigned list while you fetched employee details. Insert case is simple where you know the newly assigned skills are not present by comparing new values with session values. For the delete case, you need to know which skills have been removed by again comparing new values with session values.
Once you have both data sets you can separately execute Insert and delete statements.

This should give you a head start. Good Luck!