Replace data in table problem 1

[FoxKid]

Hi all
I am having a table table1 having fields t1,t2,t3,t4,t5,t6.
now I am having another table table2 having fields t,value.
table1 is blank and table2 is having some records. I want to append table1 from table2.

I want that if the value of table2.t is 1 its value should replace in table1.t1, same as if table2.t is 5 then its value should replace in table1.t5 and so on....

I want the smallest code to solve this problem. I don't want to use if clause.

Thanks and Regards

 

[rob444]

use table2 in 0
use table1 in 0
select table2
scan
do case
case t=1
insert into table1 (t1) values (1)
case t=2
insert into table1 (t2) values (2)
case t=3
insert into table1 (t3) values (3)
case t=4
insert into table1 (t4) values (4)
case t=5
insert into table1 (t5) values (5)
case t=6
insert into table1 (t6) values (6)
endcase
endscan
return

or shorter:

use table2 in 0
use table1 in 0
select table2
scan
select table1
append blank
repl (field(table2.t)) with table2.t
select table2
endscan
return

Rob.

 

[FoxKid]

Not like this rob, actually I have done this some time before and missing it.... it was something like this

sele table2
go top
do while !eof()
sele table1
repl t&table2.t with table2.value***(missing something)
sele table2
skip
enddo

 

[StewartUK]

FoxKid,

It looks to me that Rob's second suggestion is exactly the same as you have in your reply, except he is using SCAN...ENDSCAN instead of DO...ENDDO.

SCAN is much better because it always starts at the top of the table (unless you say something else) AND returns you to table2 AND does the skip itself.

So "SCAN" replaces your "DO WHILE" and "ENDSCAN" replaces your "sele table2" & "skip". It works out better and faster.

You said Table1 is blank (I presume you mean empty - no records?) in which case the REPL that you have will cause an error because there is nothing to replace which is why Rob has put append blank.

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[rob444]

If you only have 1 blank record in table1 and you want to keep it that way it looks like this:

use table2 in 0
use table1 in 0
select table2
scan
repl (field(table2.t,'table1')) with table1.value in table1
endscan
return


It is a somewhat strange solution because if table2 contains records with the same t value the value of the value field is overwritten.

Rob.

 

[craigsboyd]

The problem with the solutions as proposed that I can see is that they require the fields to be in a specific order the relates to the field name...t1 = first field, t2 = second field, and so on. Also, in the last example given "table1.value" should read "table2.value" I believe.

StewartUK makes some very valid points about the performance increase you will see when using the Scan...Endscan looping structure as opposed to the Do...Enddo when needing to iterate through an entire data set.

While Macro Substitution is a bit of a resource drain and should be avoided when possible and another solution presents itself, it is a viable option in this case (your statement is syntactically incorrect foxkid - would be nice if you coudl do a replace all like that, but unfortunately you can't). Here is a working example that can be cut-n-paste into VFP and run...

*!* Just some setup code for the example
CREATE CURSOR table1 (t1 I, t2 I, t3 I, t4 I, t5 I)
APPEND BLANK IN ("table1")

CREATE CURSOR table2 (t I, value I)
INSERT INTO table2 (t, value) VALUES (1, 1111)
INSERT INTO table2 (t, value) VALUES (2, 2222)
INSERT INTO table2 (t, value) VALUES (3, 3333)
INSERT INTO table2 (t, value) VALUES (4, 4444)
INSERT INTO table2 (t, value) VALUES (5, 5555)

*!* OK, now for the actual working code *********
[COLOR=blue]LOCAL lcField
SELECT ("table2")
SCAN all
	lcField = "t" + ALLTRIM(STR(table2.t)) && Alltrim(Str()) is faster than Transform()
	REPLACE &lcField with table2.value IN "table1"
ENDSCAN[/color]

*!* Now that we're done lets see our results *********

SELECT ("table1")
BROWSE

http://www.sweetpotatosoftware.com

 

[FoxKid]

Sorry Actually, I am not able to explain my problem. let me try once again :

table1
------
name
address
sub1
mark1
sub2
mark2
sub3
mark3
sub4
mark4
sub5
mark5
sub6
mark6

table2
------
name
address
subcode
sub
marks


table2 is having some records of students and table1 is blank

now the code is :
sele table2
scan
do case
case table2.subcode=1
sele table1
append blank
repl name with table2.name,;
address with table2.address,;
sub1 with sub,;
mark1 with marks
case table2.subcode=2
sele table1
append blank
repl name with table2.name,;
address with table2.address,;
sub2 with sub,;
mark2 with marks
case table2.subcode=3
sele table1
append blank
repl name with table2.name,;
address with table2.address,;
sub3 with sub,;
mark3 with marks
&& and so on................
endcase
endscan


I don't want to write so much long codes. Is there any way to short it. For previlage I have shown here less number of fields, but I am having about 10 fields as sub1,sub2....sub10.
Please help me. I think now I am able to explain you my problem.

 

[craigsboyd]

Sure, just a variation on the macro substitution approach outlined above:

LOCAL lcMark, lcSub, lcNumber
SELECT ("table2")
SCAN All
	lcNumber = ALLTRIM(STR(table2.subcode))
	lcMark = "mark" + lcNumber
	lcSub =  "sub" + lcNumber
	INSERT INTO table1 (Name, address, &lcSub, &lcMark) ;
		VALUES (table2.Name, table2.address, table2.sub, table2.marks)
ENDSCAN

...to make the code even cleaner and more professional you could create a couple of constants (#DEFINE) for "mark" and "sub" and use these constants instead inside the scan...endscan loop, but the code above will work as is.

http://www.sweetpotatosoftware.com

 

[FoxKid]

Thanks craigsboyd

This was exactly I wanted. Thanks for our reply & a star 4 u