Solved

Update on same table with criteria on records

Posted on 2007-11-25
9
330 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

820 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