SELECT and UPDATE in one statement
-
Hi everyone I'm working a program that needs to interface with a dBase 3 and pull records (sometimes limited amount) based on a zip code field. Also i need to pull the oldest records first by using MAILED field. I came up with this query to pull the data: SELECT TOP 5 * INTO DESTINATON.dbf FROM SOURCE.dbf WHERE ZIP5 = '95003' ORDER BY MAILED ASC This query works. It select top 5 record, creates DESTINATON.dbf witch is identical in structure to SOURCE.dbf and populates it with selected records. The hard part is to update the MAILED field in the same query, so next time I don't use the same records. There in no unique field that i can use to match pulled records to the SOURCE.dbf and update it. I want to do it all in one shot, but I'm not sure that this is possible. I'm using ADO 2.7 Any help is greatly appreciated. Thank you Ilya :eek:
-
Hi everyone I'm working a program that needs to interface with a dBase 3 and pull records (sometimes limited amount) based on a zip code field. Also i need to pull the oldest records first by using MAILED field. I came up with this query to pull the data: SELECT TOP 5 * INTO DESTINATON.dbf FROM SOURCE.dbf WHERE ZIP5 = '95003' ORDER BY MAILED ASC This query works. It select top 5 record, creates DESTINATON.dbf witch is identical in structure to SOURCE.dbf and populates it with selected records. The hard part is to update the MAILED field in the same query, so next time I don't use the same records. There in no unique field that i can use to match pulled records to the SOURCE.dbf and update it. I want to do it all in one shot, but I'm not sure that this is possible. I'm using ADO 2.7 Any help is greatly appreciated. Thank you Ilya :eek:
Try this with your appropriate primary key field name substitution. Not sure about your dbf suffix as it's been about 8 years since I touched dbase. SELECT TOP 5 SOURCE.* INTO DESTINATON FROM SOURCE LEFT JOIN DESTINATON on DESTINATON.PRIMARYKEY = SOURCE.PRIMARYKEY WHERE SOURCE.ZIP5 = '95003' AND DESTINATON.PRIMARYKEY IS NULL ORDER BY SOURCE.MAILED ASC Janya