CODEWITHSUNDEEP
ms-accessms-access-2007
KSM
KSM

Reputation: 262

How to make an update query

I have a table with five fields,

| Zip code | Data 1 | Data 2 | Data 3 | Code |

my table and its data below i have tones of records like this so there are many duplicate data for fields [data 1], [data2] and [data 3]

 Zip | Data 1 | Data 2 | Data 3 | Code |
 123 | __A__ |__ B__ |__ C__ |_____ |
 234 | __A__ |__ B__ |__ C__ |_____ |
 456 | __A__ |__ B__ |______ |_____ |
 678 | __A__ |__ B__ |__ C__ |_____ |
 981 | __A__ |__ B__ |______ |_____ |

my goal is to find the matching row values so i have a set of unique row from them (which i created by a query this gave me )

| Data 1 | Data 2 | Data 3 | Code |
| __A__ |__ B__ |__ C__ |_____ |
| __A__ |__ B__ |______ |_____ |

What i wanted to do with this was just enter a code value in that query so it reflects on the existing table and displays the code in all duplicate values ,

so say i enter this data in the query,( which i cannot enter for some reason )

| Data 1 | Data 2 | Data 3 | Code |
| __A__ |__ B__ |__ C__ |__C1__ |
| __A__ |__ B__ |______ |__C2__ |

i want the following output in the table

Zip | Data 1 | Data 2 | Data 3 | Code |
123 | __A__ |__ B__ |__ C__ |__C1__ |
234 | __A__ |__ B__ |__ C__ |__C1__ |
456 | __A__ |__ B__ |______ |__C2__ |
678 | __A__ |__ B__ |__ C__ |__C1__ |
981 | __A__ |__ B__ |______ |__C2__ |

How can i do this ?

Upvotes: 0

Views: 301

Answers (1)

jmacinnes
jmacinnes

Reputation: 1609

First, select your distinct values and insert them into a temporary table or scratch table:

TempTable
Data1
Data2
Data3
Code null

Next, insert distinct rows into your table

insert into TempTable (Data1, Data2, Data3)
select distinct data1, data2, data3 from myTable

Next, update your temp table with whatever code you want for each record. Still not sure if you have a specific value in mind or if you want it to be just a "unique" value. If a unique value, you could set code to be an identity and skip this step

update temptable set Code = '' where Data1 = x and Data2 = y and Data3 = z

Lastly, update your original table with the values

update myTable
inner join tempTable 
   on [myTable].data1 = [tempTable].data1 
      and [myTable].data2 = [tempTable].data2 
      and [myTable].data3 = [tempTable].data3
  set [myTable].Code = [tempTable].Code;

Upvotes: 1

Related Questions