Update...From....Where... doesn't work when the source table is buffered

[Rajesh Karunakaran]

Dear Friends,

I am trying to update c_dest from the values from tbl_source if id_one and id_two match.

UPDATE c_dest SET is_selected = .T., status_text = tbl_source.status_text ;
	FROM tbl_source ;
	WHERE c_dest.id_one = tbl_source.id_one AND c_dest.id_two = tbl_source.id_two

If the tbl_source is buffered and not TABLEUPDATE() done, this update doesn't update the c_dest fields from it.
I have verified both the ID fields have correct values in them.

When I did a TABLEUPDATE() on tbl_source and ran the same UPDATE command, it worked!

In fact, I have used such UPDATEs without issues. But those were all un-buffered situations I think.

Rajesh

 

[Mike Lewis]

Rajesh,

Keep in mind that the values of tbl_source.id_one and tbl_source.id_two that are used in the WHERE clause will be those from tbl_source before it was itself updated. If those old values caused the WHERE condition to fail, that would explain the behaviour you are seeing.

You can check that by looking at OLDVAL() for those two fields.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Mike,

Yes, I am inserting a record into tbl_source but at the moment I am trying to update c_dest, I have not done TABLEUPDATE() on tbl_source. Its still in buffered mode.
The c_dest is actually a cursor created through CREATE CURSOR...... NOFILTER READWRITE command.

So, just to confirm once again, you mean to say that the tbl_source values being used in UPDATE command will be the old ones if I don't do a TABLEUPDATE() on tbl_source ?

Rajesh

 

[bborissov]

If you use VFP 9 there is WITH (BUFFERING=.t.) hint.

FROM tbl_source WITH (BUFFERING=.t.)

Borislav Borissov
VFP9 SP2, SQL Server

 

[Mike Lewis]

Borislav,

So, does[tt] WITH (BUFFERING=.t.)[/tt] work with UPDATE? I thought it only applied to [tt]SELECT[/tt]?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

you mean to say that the tbl_source values being used in UPDATE command will be the old ones if I don't do a TABLEUPDATE() on tbl_source ?

Yes, that's my understanding. But you can see for yourself. Do this just before the UPDATE:

? "id_one:", "OLDVAL: ", OLDVAL("id_one", "tbl_source"), "CURVAL: ", CURVAL("id_one", "tbl_source)", ;
  "value: ", tbl_source.id_one
? "id_two:", "OLDVAL: ", OLDVAL("id_two", "tbl_source"), "CURVAL: ", CURVAL("id_two", "tbl_source)", ;
  "value: ", tbl_source.id_two

That would enable you to compare the old and new values, and the values on disk, of each of the two fields. That in turn should tell you which fields are participating in the UPDATE, and therefore how the WHERE clause is interpreted.

You could also set a breakpoint on the UPDATE, then use the debugger to look at the values of the various fields in the WHERE clause.

Mike






__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Mike,

Now I changed to SCAN...ENDSCAN for that data update.
Anyway, I will check as per your suggestion separately.

Borislav,
There is no WITH BUFFERING clause in UPDATE...FROM...

Rajesh

 

[Mike Lewis]

There is no WITH BUFFERING clause in UPDATE...FROM...

Yes, that's what I thought. But it doesn't give an error, so maybe it is supported after all. But, if so, it is not clear how it affects the update.

I changed to SCAN...ENDSCAN for that data update.

That's fine. But wouldn't a better approach have been to do a TABLEUPDATE() on tbl_source before updating c_dest?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

That's fine. But wouldn't a better approach have been to do a TABLEUPDATE() on tbl_source before updating c_dest?

No, that is a part of many other editing going on. So I cannot finalise the data at that moment.

Yes, that's what I thought. But it doesn't give an error, so maybe it is supported after all. But, if so, it is not clear how it affects the update.

Mike/Borislav,
Yes, I will check this. Let's see what happens.

Rajesh