Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Update on same table with criteria on records

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
cukcuk
Asked:
cukcuk
  • 5
  • 4
1 Solution
 
imitchieCommented:
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
 
cukcukAuthor Commented:
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
 
imitchieCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
cukcukAuthor Commented:
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
 
imitchieCommented:
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
 
cukcukAuthor Commented:
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
 
imitchieCommented:
you're stretching MS Access... I'm sorry but I'm at the end of my ability with this!
0
 
cukcukAuthor Commented:
helpful! =)
0
 
cukcukAuthor Commented:
Hmm... thanks for helping imitchie.

Guess i'll need to find my way with your solution provided..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now