SQL Update related data when new table and data is added

Asked Jan 23 '19 at 15:29

Active Jan 23 '19 at 16:23

Viewed 34 times

I have a table SESSIONS and I am adding a new field ContentId to this table. CONTENTs is a new table being created in the upgrade/migration script too. before a foreign key constraint is added, I need to set the values of COntentId in the SESSIONS table based upon the data in the newly created and populated table CONTENTS.

The content table has some backward compatibilty data inserted into it, such that one content is associated with one session. (Though in future, a content might have multiple sessions)

This is my query:

UPDATE ModuleSessions MS 
SET ModuleContentId = 
  (SELECT ID FROM ModuleContent MC WHERE 
  Name = CONCAT('Content For Session ', MS.Id))

It is unhappy about the MS.Id (Incorrect Syntax near 'MS')

Maybe there is a better way to do the subquery to get the value to update the SESSION?

edited Jan 23 '19 at 16:23

Steve-o169

2,066
1
12
21

asked Jan 23 '19 at 15:29

Simon

1

You need to use a join in your update. – Sean Lange Jan 23 '19 at 15:37
1

See [this question](https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server/1604212#1604212) – Sean Lange Jan 23 '19 at 15:38
Thank you for the pointers. The actual error wasn't very searchable, but using that other update table join way has solved the issue. – Simon Jan 23 '19 at 16:07

SQL Update related data when new table and data is added

0 Answers0