"copy" column from one table to another 1

[balushen]

Hello,

Im using FoxPro 6 and I have 2 tables, both with 2 columns. The first one (A)
has one column filled with ids and the second column is empty. The second
table (B) has also the same ids and its second column has some values. My
question is how can I insert the values from table B into table A (for the
same id of course).

I already tried everything I came up with including "UPDATE TableA SET Column2 = B.Column2 FROM TableA as A ,TableB as B WHERE A.Id = B.Id"
and it gets me an error message "Command contains unrecognized phrase/keyword"

Thank you in advance.
Peter

 

[Mike Lewis]

Peter,

You're close, but not quite there.

I think the following should work:

UPDATE TableA SET Column2 = TableB.Column2 ;
  FROM TableB ;
  WHERE TableB.ID = TableA.ID

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[balushen]

Tried it this way too - doesn't work

I got some help and managed to do it this way
REPLACE column2 WITH LOOKUP(tableB.column2,tableA.id,tableB.id,'id') ALL IN tableA

using index on tableB

But I will be glad to learn another way to do it too

 

[Mike Lewis]

You say my suggestion doesn't work. In what way doesn't it work? Did you get an error message? Was the target column still blank? Or what?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[balushen]

the result is the same with writing this:

UPDATE TableA SET Column2 = B.Column2 ;
FROM TableA as A ,TableB as B ;
WHERE A.Id = B.Id

an error message "Command contains unrecognized phrase/keyword

 

[Mike Lewis]

Are you sure you copied the code exactly?

I just did the following:

CREATE TABLE TableA (id I, Column2 I)
CREATE TABLE TableB (id I, Column2 I)
INSERT INTO TableA (id, column2) VALUES (1, 0)
INSERT INTO Tableb (id, column2) VALUES (1, 9)

UPDATE TableA SET Column2 = TableB.Column2 ;
  FROM TableB ;
  WHERE TableB.ID = TableA.ID

As expected, in placed the 9 from TableB into Column2 of TableA.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[balushen]

I did copy/paste to the last fragment and run it and the result was the same error..

I know it SHOULD work.. it was my first idea to do this but it doesn't work..

it does work on VFP9 but on VFP6 it does not

 

[Mike Lewis]

I hadn't realised you were using VFP 6. That would explain it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

Alternatively you could set up a RELATION between the tables and do the REPLACE.

Something like:

* --- Create An Index On Child Table ID Field ---
USE TableB IN 0 EXCLUSIVE
SELECT TableB
INDEX ON ID TAG ID

* --- Establish A Relation Between 2 Tables Using ID Field ---
USE TableA IN 0
SELECT TableA
SET RELATION TO ID INTO TableB

* --- Now Populate TableA-Column B Where ID's Match ---
REPLACE ALL TableA.B WITH TableB.B;
   FOR !EMPTY(TableB.ID)

Good Luck,
JRB-Bldr

 

[balushen]

This works too, thank you!