Pull from one table to fill a record in another

[Thumper2004]

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

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.

 

[TamarGranor]

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.

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.

 

[Olaf Doschke]

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:

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:

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.

 

[Thumper2004]

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.

 

[Thumper2004]

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.

 

[Olaf Doschke]

I can replace the tables that are modified with the originals

fine

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:

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())

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

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 [tt]UPDATE table1 t1[/tt], you can only write [tt]UPDATE t1.... FROM table1 t1[/tt]

Bye, Olaf.

 

[Thumper2004]

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

 

[Thumper2004]

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

 

[Olaf Doschke]

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.

 

[Thumper2004]

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

 

[Olaf Doschke]

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.

 

[Thumper2004]

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!