×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Pull from one table to fill a record in another

Pull from one table to fill a record in another

Pull from one table to fill a record in another

(OP)
I want to update table 1 field 1 with a value from table 2 field 1 and keep getting subquery returns more than one record.

update table 1 field 1;
from table 2 field 1;
where table1.key=table2.key

CODE -->

UPDATE table1 t1;
SET field 1 =;
 (select t2.field1;
	from table1 t1;
	join table2 t2 on t1.key=t2.key;
	where t1.field2 BETWEEN CTOD("01/01/1900") and CTOD("04/23/2018") and t1.field1 = ctod("")) 

thanks for any help!

vfp9 is what we are using.

RE: Pull from one table to fill a record in another

Based on your other posts, I'm going to guess that you don't have actually have any records with dates before 1900 and simplify that part.

CODE

UPDATE Table1 T1 ;
   SET field1 = T2.Field1 ;
   WHERE T1.key = T2.key ;
     AND T1.Field2 <= DATE(2008, 4, 23) ;
     AND EMPTY(T1.Field1) 

Untested, of course, but I think this should do the trick.

RE: Pull from one table to fill a record in another

You're moving in the wrong direction thinking that was the problem of the update not working. Tamar's update works syntactically but uses T2 as given alias name of a table not directly involved in the query, only reading one record of it, the current record.

Since you already did wrong updates, before we start discussing how to do correlated updates (you had that join aspect right already), please check how many records are empty in field1, so they are updated at all. First just select instead of updating:

CODE -->

Select * FROM table1 t1 WHERE EMPTY(T1.Field1) 

Does that give any records? If not, then most likely because as you said T1.Field1 was populated with a wrong date. Then it isn't empty anymore. You can write as many correct update statements as you like, if the condition to qualify for an update is wrong, no record is updated. First, revert to your original state.

Now about how to do an update in table1 from table2, that is written as you already did earlier:

CODE

Update Table1 t1;
   Set Field1 = t2.Field1 ;
   From Table2 t2;
   Where t2.key = t1.key 
Table3 can also be joined as you already did.

Bye, Olaf.

RE: Pull from one table to fill a record in another

(OP)
Thanks TamarGranor, but I have to qualify the lower end of the date range because empty is considered less than and we don't want to consider empty.

I also have to reference table 2 unless the table is table2.field1

TamarGranor (Programmer)3 May 18 20:24
Based on your other posts, I'm going to guess that you don't have actually have any records with dates before 1900 and simplify that part.

CODE
UPDATE Table1 T1 ;
SET field1 = T2.Field1 ;
WHERE T1.key = T2.key ;
AND T1.Field2 <= DATE(2008, 4, 23) ;
AND EMPTY(T1.Field1)

Untested, of course, but I think this should do the trick.

RE: Pull from one table to fill a record in another

(OP)
Olaf, I have the original data. The updates I am doing are on a copy database and I can replace the tables that are modified with the originals and be back to square. Every attempt to fill the t1.field1 with t2.field1 results in every record t1.field1 being changed to the t2.field1 value. I need it to fill the t1.field1 for each t1.key=t2.key with only the value in t2.field for the matching keys.

RE: Pull from one table to fill a record in another

Quote (Thumper2004)

I can replace the tables that are modified with the originals
fine

Quote (Thumper2004)

I need it to fill the t1.field1 for each t1.key=t2.key with only the value in t2.field for the matching keys.

Well, change my update from above to this:

CODE

CREATE CURSOR table1 (key int, field1 date)
INSERT INTO table1 VALUES (1, {})

CREATE CURSOR table2 (key int, field1 date)
INSERT INTO table2 VALUES (1, DATE())

Update t1 ;
   Set Field1 = t2.Field1 ;
   From Table1 t1;
   Join Table2 t2 on t2.key = t1.key ;
   Where .T. 

Quote (vfp help)

UPDATE Target... Target specifies a target table, cursor, table or cursor alias, or file to update...Alias specifies an alias that matches a table in the FROM clause or a cursor in the current data session for the update operation.

So to use an alias t1 you can't write UPDATE table1 t1, you can only write UPDATE t1.... FROM table1 t1

Bye, Olaf.

RE: Pull from one table to fill a record in another

(OP)
Thanks Olaf. I will give that a try tomorrow. It’s been a frustrating conundrum! Thanks again !!

RE: Pull from one table to fill a record in another

(OP)
OK So i am not a developer, how do you populate the cursor with what already exists in the other tables.

RE: Pull from one table to fill a record in another

You don't need the cursors, they are just demonstrating that the Update-SQL works, written that way. I fill in a record in the cursor table1 without a date, that corresponds to table1 with empty field1, I fill in a record with current date into cursor table2, and then the Update does what you need, it copies this date over. To verify it doesn't copy the same value into all records, you could add a second record to both tables empty in table1 and with another date in table2, but all you need from the sample code is the Update-SQL I highlighted bold (for that very reason), adapted to your situation, especially with your WHERE clause instead of WHERE .T.

In essence, cursors are tables, so as that Update SQL works for cursors alias named table1 and table2, it also works for table1.dbf and table2.dbf files. But to see that I just created cursors, as that spares to dispose any files and I'm still sure it works exactly that way with dbf files.

Bye, Olaf.

RE: Pull from one table to fill a record in another

(OP)
So the issue was finally identified as table2 returning multiple records for the primary key/foreign key correlation from table1 to table2. So we told it to only return the max key for each t1 primary key = t2 foreign key. Thanks for all your help guys on this and the other one as well. I wish I could show yall the final code, but that would get me fired.

from table1 t1;
join (select max(primkey) as maxprimkey, forkey from table2 group by forkey) as maxfd on t1.primkey=maxfd.forkey;
join table2 t2 on t2.primkey=maxfd.maxprimkey;

BROWE

RE: Pull from one table to fill a record in another

OK, nobody could have told you that's your problem if you don't specify you have a 1:n relation. I was thinking about syncing two similar tables of two locations/stores or anything like that.

If you have multiple detail records, of course, there is one taken last. What you told sounded like all records, really all records across multiple keys got the same date.

Bye, Olaf.

RE: Pull from one table to fill a record in another

(OP)
That is what happened Olaf originally, which is what brought me out here trying to find help. The consideration that table2 had a 1:n relation was not taken into account. Once that was taken into account and the adjustments made to only return one record out of table2 for each primkey from table1 it was all good from there. Again I appreciate the thought and explanation that was provided.

Have a great weekend!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close