cukcuk
asked on
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?
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?
ASKER
I tried to run the update, and it returns a
Syntax error (missing operator) in query expression 'D' ......
Does Access support the FROM method?
Syntax error (missing operator) in query expression 'D' ......
Does Access support the FROM method?
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
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
ASKER
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..
BTW, i'm using Access's Queries Object to type the SQL and tries to view in Datasheet view..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
you're stretching MS Access... I'm sorry but I'm at the end of my ability with this!
ASKER
helpful! =)
ASKER
Hmm... thanks for helping imitchie.
Guess i'll need to find my way with your solution provided..
Guess i'll need to find my way with your solution provided..
Open in new window