How do I create a mass update query?

[robmoors]

I am new to Foxpro and new to SQL. I don't have problems using SQL to update a single table, but what if I am trying to update data in table A from the data in table B. I am experienced in Access and do this all the time. Here is the code in Access:

UPDATE tbl_inventory INNER JOIN 01_03_02Accounts ON tbl_inventory.[Account Number] = [01_03_02Accounts].account_nu SET [01_03_02Accounts].cdDays = [tbl_inventory].[cdDays];

How do you do it in Foxpro?

 

[DEDMOD]

In FoxPro the UPDATE command only works on a single table. However it would be easy enough to create an updatable view or cursor and then run a SCAN on the resulting view to make the changes desired.

CREATE SQL VIEW myView AS;
SELECT ti.AccountNumber AS oldAN, ti.cdDays AS oldDays , a01.cddays AS newDays,; a01.account_nu AS newAN;
   FROM tbl_inventory ti Join 01_03_02Accounts a01;
   WHERE  ti.AccountNumber = a01.account_nu
USE myView
SCAN
   REPLACE newDays WITH oldDays
ENDSCAN

I think I've got the syntax more or less right, though I haven't tested it. In particular I don't know if the view created is updatable or not. But presumably someone will come along and correct me if I'm wrong.

Dave Dardinger

 

[robmoors]

Thank you Dave, you've been alot of help. One more thing. My code reads like this:

USE myView
Do While .T.
REPLACE myView.oldDays with 100
SKIP
IF EOF()
EXIT
END IF
END DO

Now when I browse in the view after, I see that the changes were made, how do I make those changes update on the table. I tried using TABLEUPDATE() and TABLEUPDATE(.T.), and it doesn't work. What should I do?

-

 

[chpicker]

I don't know if it's important, but I would change this line:

REPLACE myView.oldDays with 100

with this:

REPLACE oldDays with 100 IN myView

I don't think that will help with your actual problem, but it can cause problems down the line if you use "table.field" with the REPLACE command. I did it that way for a long time, then ran into a big problem one day that switching to the second syntax fixed. So, I've been getting myself into the habit of using the IN clause instead of prefacing the fields with the table name.

As for your actual problem, there are a couple of things I can think of offhand. Is the view you created updateable? Does your original table have a Primary Key? If it doesn't have a primary key, then SQL won't know which record in the table to match to the record in the View that you're trying to update. If it isn't updateable, it won't accept changes at all. However, I think it would at least raise an error if you tried.

I hope this isn't too confusing (

 

[chpicker]

Ok, I looked in the MSDN help file, and it appears that SQL Views default to NOT being updateable. Execute this line, and it SHOULD work:

DBSETPROP("myView","SendUpdates",.T.)

Again, your table should have a primary key, and that primary key needs to be one of the fields in the View, otherwise SQL won't know which records are supposed to be updated.

Hope this helps!

 

[DEDMOD]

Well this is what I was talking about when I said I don't know what you have to do to make an view from code updatable. A view which you create in the view designer has a tab which lets you decide. I just went off and created a couple of programs to emulate my code. It appears that the view created doesn't allow updates, though once created, you can go in and modify it to allow updates. When I did that and ran the scan / replace code, my base table did indeed show the updates.

After some more effort I've (re)discovered that some DBSETPROP() calls are needed. But so far I haven't gotten any to work. I've been trying:

= DBsetPROP('MyView','View','SendUpdates', .T.)

and

 = DBsetPROP('MyView.newDays','Field','Updatable', .T.)

but they don't seem to change anything in the View. There are probably some others which need to be done. Hopefully someone else will chime in who's more familiar with doing views via code.

Dave Dardinger