2

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:

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:

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.

I'm at a loss. Help?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
somacore
  • 6,294
  • 3
  • 24
  • 19

4 Answers4

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

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

Mark Biek
  • 146,731
  • 54
  • 156
  • 201
  • Thanks for the thorough explanation. You were beaten on the answer but I wanted to let you know this was very helpful as well. – somacore Jan 30 '09 at 18:28
  • Glad it was useful. I figured some explanation of how that sort of nested query works might come in handy down the road. – Mark Biek Jan 30 '09 at 19:08
1

This should work:

UPDATE table SET VB_Flag = 1 
WHERE TicketNum IN (SELECT TicketNum FROM Table WHERE program_type = 'VB')
DJ.
  • 16,045
  • 3
  • 42
  • 46
  • Thanks! I had played around with the TicketNum IN () stuff but couldn't quite figure it out. This got it! – somacore Jan 30 '09 at 18:28
1

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

John Mo
  • 1,326
  • 10
  • 14
0

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')
Traingamer
  • 1,438
  • 8
  • 9