SQL Server:如何基于where子查询更新表?

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).

我正在尝试基于Jobname和max(log_id)更新endTime。

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?

如何得到这份工作?

4 个解决方案

#1

Take out the Jobname from the select list in the subquery.

从子查询中的select列表中取出Jobname。

You don't actually need it to get the result you need, SQL Server will still return the right log_id.

您实际上并不需要它来获得所需的结果，SQL Server仍然会返回正确的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.

由于返回两个字段(Jobname,MAX(Log_id))并试图将Log_id与之匹配，所以您将无法工作。

#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

#3

This query:

这个查询:

(SELECT JobName, MAX(log_id) AS log_id FROM dbo.lTab WHERE jobname = @Jname GROUP BY JobName)

is returning multiple results, exclude JobName from your query.

返回多个结果，将JobName排除在查询中。

#4

Yet a better option would be

然而更好的选择是

UPDATE tablename SET tablename.field1 = sourceTable.dataField
From tablename 
join sourceTable On tablename.keyfield = sourceTable.keyField
Where sourceTable.jobname = @jobName

#1