CODEWITHSUNDEEP
sqlsql-servert-sql
charlie_cat
charlie_cat

Reputation: 1850

Delete a row from a Joined table in SQL Server 2008 R2

i have two tables:

CREATE TABLE [Training].[UserTrainingModuleResults](
[UserTrainingModuleResultId] [int] IDENTITY(1,1) NOT NULL,
[TrainingModules_TrainingModuleId] [int] NOT NULL,
[Users_UserId] [int] NOT NULL,
[DateAndTimeStarted] [datetime] NOT NULL,
[DateAndTimeCompleted] [datetime] NULL,
[NumberOfQuestionsAnswered] [smallint] NOT NULL,
[NumberOfQuestionsAnsweredCorrectly] [smallint] NOT NULL,
[TrainingModuleStatuses_TrainingModuleStatusId] [smallint] NOT NULL,
  CONSTRAINT [PK_UserTrainingModuleResults] PRIMARY KEY CLUSTERED 
(
[UserTrainingModuleResultId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and

CREATE TABLE [History].[ViewTrainingModuleHistory](
[HistoryId] [int] NOT NULL,
[TrainingModules_TrainingModuleId] [int] NOT NULL,
CONSTRAINT [PK_ViewTrainingModuleHistory] PRIMARY KEY CLUSTERED 
(
[HistoryId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]

then i have a script that must delete from the ViewTrainingModuleHistory where TrainingModules_TrainingModuleId = UserTrainingModuleResults.TrainingModules_TrainingModuleId

delete from History.TrainingModuleCompletedHistory as tmch  //syntax error here
  left join Training.UserTrainingModuleResults as utmr
on utmr.UserTrainingModuleResultId = tmch.UserTrainingModuleResults_UserTrainingModuleResultId
where utmr.Users_UserId = 446 and tmch.UserTrainingModuleResults_UserTrainingModuleResultId = 110

the above is not correct, i get syntax error near "as" please help thanks

Upvotes: 1

Views: 1353

Answers (3)

Ric
Ric

Reputation: 13248

delete tmch  
from History.TrainingModuleCompletedHistory tmch
  left join Training.UserTrainingModuleResults utmr
on utmr.UserTrainingModuleResultId = tmch.UserTrainingModuleResults_UserTrainingModuleResultId
where utmr.Users_UserId = 446 and tmch.UserTrainingModuleResults_UserTrainingModuleResultId = 110

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13496

delete tmch  
from History.TrainingModuleCompletedHistory tmch  
left join Training.UserTrainingModuleResults utmr
on utmr.UserTrainingModuleResultId = tmch.UserTrainingModuleResults_UserTrainingModuleResultId
where utmr.Users_UserId = 446 
and tmch.UserTrainingModuleResults_UserTrainingModuleResultId = 110

Upvotes: 0

Taryn
Taryn

Reputation: 247680

The alias should be right after the word DELETE:

delete tmch   --- < alias of table to delete records from goes here
from History.TrainingModuleCompletedHistory tmch  
left join Training.UserTrainingModuleResults utmr
  on utmr.UserTrainingModuleResultId = tmch.UserTrainingModuleResults_UserTrainingModuleResultId
where utmr.Users_UserId = 446 
  and tmch.UserTrainingModuleResults_UserTrainingModuleResultId = 110

Upvotes: 0

Related Questions