访问数据库查询 - 需要帮助更新某些记录

I have an access DB which we use to track tickets. Each ticket may have multiple occurrences because of different programming changes associated with that ticket. Each record also has a program_type field which is SVR or VB. Example:

我有一个访问数据库,我们用它来跟踪票证。由于与该票证相关联的不同编程更改,每个票证可能有多次出现。每个记录还有一个program_type字段,它是SVR或VB。例:

123456 - SVR - SomeCode

123456 - VB - SomeVBCode

I've added a column to the database called VB_Flag, which defaults to 0, which I would like to change to the number 1 for every ticket containing VB code. So, the result here would be:

我在数据库中添加了一个名为VB_Flag的列,默认为0,我想将每个包含VB代码的票据更改为1。所以,这里的结果将是:

123456 - SVR - SomeCode - 1

123456 - VB - SomeVBCode - 1

But, I can't figure out for the life of me how to write this update query. At first I tried:

但是,我无法弄清楚如何写这个更新查询的生活。起初我尝试过:

UPDATE table SET VB_Flag = 1 WHERE program_type = 'VB'

But that obviously left out all the SVR code that shared a ticket number with the VB code.

但这显然遗漏了与VB代码共享票号的所有SVR代码。

I'm at a loss. Help?

我很茫然。救命?

4 个解决方案

#1

This should work:

这应该工作:

UPDATE table SET VB_Flag = 1 
WHERE TicketNum IN (SELECT TicketNum FROM Table WHERE program_type = 'VB')

#2

You can do something like this:

你可以这样做:

UPDATE tickets SET VB_Flag = 1
WHERE ticket_id IN (SELECT ticket_id FROM tickets WHERE program_type = 'VB');

The inner SELECT statement returns a list of all ticket_ids that have a program_type of 'VB'.

内部SELECT语句返回所有具有program_type为“VB”的ticket_id的列表。

The update then sets the VB_Flag to 1 for ALL records with one of those ticket_ids (that includes those with a program_type of 'SVR'.

然后,更新将所有记录的VB_Flag设置为1,其中包含其中一个ticket_id(包括program_type为'SVR'的那些)。

#3

UPDATE Table INNER JOIN Table AS Table2 ON Table.TicketNumber = Table2.TicketNumber SET Table2.VB_Flag = 1 WHERE (([Table].[program_type]="VB"))

UPDATE表INNER JOIN表AS表2 ON Table.TicketNumber = Table2.TicketNumber SET Table2.VB_Flag = 1 WHERE(([Table]。[program_type] =“VB”))

#4

A simple nested select should take care of your problem:

一个简单的嵌套选择应该解决您的问题:

UPDATE myTable
SET VB_Flag = 1 
WHERE TicketID in (Select TicketID from myTable WHERE program_type = 'VB')

#1