SQL Server: How to update table based on subquery in where clause?
11,987
Solution 1
Take out the Jobname
from the select list in the subquery.
You don't actually need it to get the result you need, SQL Server will still return the right log_id.
What you have won't work since you are returning 2 fields (Jobname
,MAX(Log_id)
) and trying to match Log_id
to it.
Solution 2
WITH q AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY JobName ORDER BY log_id DESC) AS rn
FROM lTab
WHERE JobName = @Jname
)
UPDATE q
SET endTime = GETDATE()
WHERE rn = 1
Author by
atricapilla
Updated on June 28, 2022Comments
-
atricapilla almost 2 years
I have a table (with data) like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[lTab]( [log_id] [int] IDENTITY(1,1) NOT NULL, [JobName] [nvarchar](40) NULL, [startTime] [datetime] NULL, [endTime] [datetime] NULL, [BatchId] [int] NULL, [status] [varchar](10) NULL, [messag] [varchar](255) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[lTab] ON INSERT [dbo].[lTab] ([log_id], [JobName], [startTime], [endTime], [BatchId], [status], [messag]) VALUES (1, N'Job1', CAST(0x00009EB700FBF56F AS DateTime), NULL, 2, N'START', N'Test') INSERT [dbo].[lTab] ([log_id], [JobName], [startTime], [endTime], [BatchId], [status], [messag]) VALUES (2, N'Job2', NULL, CAST(0x00009EB700FBF975 AS DateTime), 2, N'START', N'Test') INSERT [dbo].[lTab] ([log_id], [JobName], [startTime], [endTime], [BatchId], [status], [messag]) VALUES (3, N'Job3', CAST(0x00009EB700FC287F AS DateTime), NULL, 2, N'START', N'Test') INSERT [dbo].[lTab] ([log_id], [JobName], [startTime], [endTime], [BatchId], [status], [messag]) VALUES (4, N'Job3', NULL, CAST(0x00009EB700FC2CC6 AS DateTime), 2, N'END', N'Test') SET IDENTITY_INSERT [dbo].[lTab] OFF
I'm trying to update endTime based on Jobname and max(log_id).
DECLARE @Jname VARCHAR(10) SET @Jname = 'Job3' UPDATE lTab SET endTime = GETDATE() WHERE log_id = (SELECT JobName, MAX(log_id) AS log_id FROM dbo.lTab WHERE jobname = @Jname GROUP BY JobName)
I get an error
sg 116, Level 16, State 1, Line 6 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
How to get this work?