Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update on same table with criteria on records

Posted on 2007-11-25
9
Medium Priority
?
353 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 800 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

564 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