CODEWITHSUNDEEP
sqlsql-servermany-to-manysql-update
user9807720
user9807720

Reputation:

How to update the Bridge table in SQL Server?

I have three tables tblSubject, tblSubjectGroup and tblSubjectGroupDetails.

tblSubject table has the following columns:

subId (PK, char(36), not null)
subName (varchar(50), not null)

tblSubjectGroup table has these columns:

subGroupId (PK, char(36), not null)
subGroupName (varchar(50), not null)

tblSubjectGroupDetails is the bridge table between the above tables which have a many-to-many relationship:

subGroupId (FK, char(36), not null)
subId (FK, char(36), not null)

I put the following dummy data in these tables:

tblSubject:

English, Mathematics, Physics, Chemistry, Science, Biology, Economics, Geography, History

tblSubjectGroup:

Group1, Group2,Group3

Here, I want to update the Group2 with these subjects: Geography, History, Science.

(Inserting and deleting is fine for me, but I can't figure out how to update the bridge table)

UPDATE tblSubjectGroupDetails 
SET subId = '......' 
WHERE subGroupId = '......'

The above statement will change all the subjects of a particular subjectGroup. I want to change only Economics to Science for Group2.

By the way, I forgot to mention that tblSubjectGroupDetails has a composite primary key.

How can I do this in SQL Server?

Upvotes: 0

Views: 1171

Answers (1)

Junjie
Junjie

Reputation: 521

I want to change only Economics to Science for Group2.

You need another condition in your where clause, like so:

UPDATE tblSubjectGroupDetails SET subId = science_id
WHERE subGroupId = group_2_id
  and subId = economics_id;

Just replace the IDs with the correct values.

Of course, this will not work (will generate a primary key constraint violation) if your Group2 has already a Science subject record in that table.

Upvotes: 0

Related Questions