Join 2 tables then update table 2 fields with table 1 field - Visual foxpro 6

Question

Edit: Visual Foxpro 6

i know im asking something way too old but i have to do it anyways.

i am in need of help, basically this is the scenario:

i have 2 tables, tableA and tableB. they share common fields i.e. name and date and info.

what i need is to have tableA.info field be updated by tableB.info based on tableA.name = tableB.name and tableA.date = tableB.date

i can picture this clearly in my head, but i cannot get the syntax to work. Please help! Very much appreciated

Answer 1

Update tableA ;
  set Info = tableB.info ;
from tableB ;
where tableA.Name == tableB.Name and tableA.Date = tableB.Date

Would do it. Here is a sample:

CREATE CURSOR TableA (Name c(10), Date d, Info c(50))
CREATE CURSOR TableB (Name c(10), Date d, Info c(50))

INSERT INTO TableA (Name, Date, Info) VALUES ('n1', {^2023/01/01},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n1', {^2023/01/02},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n1', {^2023/01/03},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n2', {^2023/01/01},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n2', {^2023/01/02},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n2', {^2023/01/03},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n3', {^2023/01/03},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n4', {^2023/01/04},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n5', {^2023/01/05},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n6', {^2023/01/06},'')

INSERT INTO TableB (Name, Date, Info) VALUES ('n1', {^2023/01/01},'N1 - 0101')
INSERT INTO TableB (Name, Date, Info) VALUES ('n1', {^2023/01/02},'N1 - 0102')
INSERT INTO TableB (Name, Date, Info) VALUES ('n2', {^2023/01/02},'N2 - 0102')
INSERT INTO TableB (Name, Date, Info) VALUES ('n2', {^2023/01/03},'N2 - 0103')
INSERT INTO TableB (Name, Date, Info) VALUES ('n3', {^2023/01/03},'N3 - 0103')
INSERT INTO TableB (Name, Date, Info) VALUES ('n5', {^2023/01/05},'N5 - 0105')



Update tableA ;
  set Info = tableB.info ;
from tableB ;
where tableA.Name == tableB.Name and tableA.Date = tableB.Date

select * from tableA

EDIT: Using VFPOLEDB you can execute VFP9 compatible SQL commands from within older versions like VFP8,7,6,5,(and likely 3). ie: Below is a sample tested in VFP 5. Sampled in d:\VFP5 folder.

SET exclusive off
CREATE table 'd:\vfp5\TableA' (Name c(10), Date d, Info c(50))
CREATE table 'd:\vfp5\TableB' (Name c(10), Date d, Info c(50))

INSERT INTO TableA (Name, Date, Info) VALUES ('n1', {^2023/01/01},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n1', {^2023/01/02},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n1', {^2023/01/03},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n2', {^2023/01/01},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n2', {^2023/01/02},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n2', {^2023/01/03},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n3', {^2023/01/03},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n4', {^2023/01/04},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n5', {^2023/01/05},'')
INSERT INTO TableA (Name, Date, Info) VALUES ('n6', {^2023/01/06},'')

INSERT INTO TableB (Name, Date, Info) VALUES ('n1', {^2023/01/01},'N1 - 0101')
INSERT INTO TableB (Name, Date, Info) VALUES ('n1', {^2023/01/02},'N1 - 0102')
INSERT INTO TableB (Name, Date, Info) VALUES ('n2', {^2023/01/02},'N2 - 0102')
INSERT INTO TableB (Name, Date, Info) VALUES ('n2', {^2023/01/03},'N2 - 0103')
INSERT INTO TableB (Name, Date, Info) VALUES ('n3', {^2023/01/03},'N3 - 0103')
INSERT INTO TableB (Name, Date, Info) VALUES ('n5', {^2023/01/05},'N5 - 0105')

use in (select('tableA'))
use in (select('tableB'))

lcDataloc = 'd:\VFP5'
lcSQL = ;
  "Update tableA "+;
  "  set Info = tableB.info "+;
  "  from tableB "+;
  "  where tableA.Name == tableB.Name and tableA.Date = tableB.Date"

oCon = createobject('adodb.connection')
oCon.ConnectionString = "Provider=VFPOLEDB;Data Source="+m.lcDataloc
oCon.Open()
oCon.Execute(m.lcSQL)
oCon.Close

SELECT * from TableA