Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update on same table with criteria on records

Posted on 2007-11-25
9
Medium Priority
?
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

722 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