Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Dianne Benedicto
  Where is Dianne Benedicto?
 Davao City
 Philippines
 Dianne Benedicto
 Tags
Subject: RE: replacing fields using SQL statements?
Thread ID: 188802 Message ID: 188867 # Views: 1 # Ratings: 1
Version: Visual FoxPro 7 Category: Databases, Tables and SQL Server
Date: Tuesday, August 12, 2008 11:03:29 AM         
   


> Hi! Thanks for the help. I already run the form but the output is not the one I wanted. I need to copy/replace the student.studentid to personal.studentid. The two tables don't have the same number of records since student table was imported from SQL database. Please help!

Hello Dianne
Please forget about the UPDATE commands Christian and Surinder have proposed.
UPDATE TableA FROM TableB was introduced in VFP9; it's not suppoerted in VFP7, as a quick look in the Help for VFP7 would show you.
By the way, it's not even supported in Standard SQL; VFP9 has borrowed it from Microsoft SQL Server's T-SQL language.
You could use that kind syntax directly in the SQL Server database over an ODBC connection.

Try this in VFP7
SET DATABASE TO Gdance
SELECT S.studentid, S.firstname, S.lastname ;
 FROM Students S JOIN Personal P ;
 ON S.student_id=P.studentid ;
 INTO CURSOR Q1 

INDEX ON studentid TAG Studentid

UPDATE Personal SET firstname = Q1.firstname, ;
 lastname=Q1.lastname ;
 WHERE SEEK(studentid, "Q1" )

or 
SELECT Personal 
 REPLACE lastname WITH Q1.lastname, ;
 firstname WITH Q1.firstname ;
 FOR SEEK(studentid, "Q1")


As you see I'm not bothering with replacing only where Student has a different name than Personal for the same studentid. It will come out the same in the end anyway.
I hope your backups are ok before playing around with this :- )?

-Anders

ENTIRE THREAD

replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 7:32:01 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 7:51:41 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 8:50:13 AM
RE: replacing fields using SQL statements? Posted by surinder singh @ 8/12/2008 8:58:04 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 8:59:56 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 9:13:52 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 9:21:39 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 9:26:53 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 9:31:13 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 9:42:44 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 9:51:47 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 9:32:13 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 9:36:22 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 9:40:11 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 9:47:46 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 9:51:54 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 10:08:44 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 10:15:04 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 10:30:31 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 10:04:03 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 10:06:09 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 10:08:47 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 10:16:18 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 10:16:33 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 10:17:46 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 10:33:16 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 10:38:20 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 10:46:36 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 10:50:52 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 10:57:30 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 10:55:20 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/12/2008 11:04:20 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 10:15:09 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 10:17:07 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 10:41:58 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 10:45:06 AM
RE: replacing fields using SQL statements? Posted by Anders Altberg @ 8/12/2008 11:03:29 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/12/2008 11:34:10 AM
RE: replacing fields using SQL statements? Posted by Anders Altberg @ 8/12/2008 11:39:25 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 12:03:04 PM
RE: replacing fields using SQL statements? Posted by Anders Altberg @ 8/12/2008 12:06:56 PM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/12/2008 12:16:09 PM
RE: replacing fields using SQL statements? Posted by Anders Altberg @ 8/12/2008 2:37:59 PM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/13/2008 1:37:43 AM
RE: replacing fields using SQL statements? Posted by suhas hegde @ 8/18/2008 9:34:27 AM
RE: replacing fields using SQL statements? Posted by Anders Altberg @ 8/18/2008 12:06:57 PM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/14/2008 2:54:04 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/19/2008 3:16:17 AM
RE: replacing fields using SQL statements? Posted by Christian Tabligan @ 8/19/2008 6:30:36 AM
RE: replacing fields using SQL statements? Posted by Anders Altberg @ 8/14/2008 5:54:42 PM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/18/2008 3:08:54 AM
RE: replacing fields using SQL statements? Posted by Jim Booth @ 8/18/2008 4:06:05 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/18/2008 6:22:33 AM
RE: replacing fields using SQL statements? Posted by Anders Altberg @ 8/18/2008 11:58:04 AM
RE: replacing fields using SQL statements? Posted by Glenn Villar @ 8/18/2008 12:29:21 PM
RE: replacing fields using SQL statements? Posted by lopera @ 8/14/2008 3:27:18 AM
RE: replacing fields using SQL statements? Posted by Dianne Benedicto @ 8/14/2008 10:05:58 AM
RE: replacing fields using SQL statements? Posted by Anders Altberg @ 8/14/2008 6:02:54 PM