Solved

Update on same table with criteria on records

Posted on 2007-11-25
9
305 Views
Last Modified: 2013-11-27
Hi, I would like to update a record (e.g.: record 3) on Access table with a criteria in another record (e.g. record 1).

So in this case, for a matching ID in A, with the STATUS "DEL", I will need to update the DOC field of both record 1 and record 3 to a new value "D", & copy the INFO field from record 1 (old data) to record 3 (new data).

E.G.:

UID - ID - STATUS - DOC - INFO
1 - A - APP - C - XXX
2 - B - APP - C - XXX
3 - A - DEL - NULL - NULL
4 - C - APP - C - XXX
5 - D - APP - C - XXX

Must it be done using 2 tables to compare?
0
Comment
Question by:cukcuk
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20348234
try this. assumes that you won't have more than 2 of the same ID
update d set doc = 'D', info = d.info

from

(

select a.id, a.info

from mytable a, mytable b

where a.id = b.id and b.status = 'DEL'

) c, mytable d

where d.id = c.id

Open in new window

0
 

Author Comment

by:cukcuk
ID: 20348501
I tried to run the update, and it returns a
Syntax error (missing operator) in query expression 'D' ......

Does Access support the FROM method?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20348584
sorry! please use double quotes

update d set doc = "D", info = d.info
from
(
select a.id, a.info
from mytable a, mytable b
where a.id = b.id and b.status = "DEL"
) c, mytable d
where d.id = c.id
0
 

Author Comment

by:cukcuk
ID: 20348637
I'm still having the same error after changing to double quotes...

BTW, i'm using Access's Queries Object to type the SQL and tries to view in Datasheet view..
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 25

Accepted Solution

by:
imitchie earned 200 total points
ID: 20354400
found it. although it makes one important assumption

where there is a record with status DEL, there should only be one other record that is NOT "DEL".
UPDATE myTable AS d INNER JOIN [select a.id, a.info

from myTable a, myTable b

where a.id = b.id and b.status = "DEL" and a.Status <> "DEL"

]. AS c ON d.id = c.id SET d.doc = 'D', d.info = c.info;

Open in new window

0
 

Author Comment

by:cukcuk
ID: 20362919
Hey it works... thanks..

But in my case.. it is abit more complicated..

It might have more than 1 DEL & APP. So the latest DEL record must be able to retrieve the data from the earliest APP record.

Would it be easier to deal with using a duplicate table of the existing one? Please advise...

e.g.:

UID - ID - STATUS - DOC - INFO
1 - A - APP - C - XXX
2 - B - APP - C - XXX
3 - A - DEL - NULL - NULL
4 - C - APP - C - XXX
5 - D - APP - C - XXX

will become

UID - ID - STATUS - DOC - INFO
1 - A - APP - D - XXX (updated)
2 - B - APP - C - XXX
3 - A - DEL - D - XXX (updated)
4 - C - APP - C - XXX
5 - D - APP - C - XXX
6 - A - APP - C - XXX (newly added record)
7 - A - DEL - NULL - NULL (newly added record)

will become

UID - ID - STATUS - DOC - INFO
1 - A - APP - D - XXX
2 - B - APP - C - XXX
3 - A - DEL - D - XXX
4 - C - APP - C - XXX
5 - D - APP - C - XXX
6 - A - APP - D - XXX (updated)
7 - A - DEL - D - XXX (updated using record 1)
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20363147
you're stretching MS Access... I'm sorry but I'm at the end of my ability with this!
0
 

Author Closing Comment

by:cukcuk
ID: 31410921
helpful! =)
0
 

Author Comment

by:cukcuk
ID: 20363554
Hmm... thanks for helping imitchie.

Guess i'll need to find my way with your solution provided..
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now