subqueries do not work for muliple updates 1

[hammertounge]

I have tried the following code and it seems to only sork for one record.

update table_1 set table_1.value1 = table_2.value2 where table_1.ID_1 = table_2.ID_2

update table_1 set table_1.value1 = table_2.value2 where table_1.ID_1 in (select ID_2 from table_2)

The queries above do not seem to work. Does anyone have any ideas It should be noted that these tables are fairly large, table_1 has over 1 million records and table_2 has about 50,000 records.

Thanks in advance.

 

[Neil Toulouse]

You could SCAN through Table_2 and update Table1 accordingly. For example:

SELECT Table_2
SCAN

UPDATE Table_1 SET Value1 = Table_2.Value2 WHERE ID_1 == Table_2.ID_2

SELECT Table_2
ENDSCAN

Should be fairly speedy.

Neil
"I love work. I can sit and stare at it for hours..."

 

[Mike Gagnon]

FatSlug
UPDATE Table_1 SET Value1 = Table_2.Value2 WHERE ID_1 == Table_2.ID_2

UPDATE SQL will do ALL records so a SCAN is not needed.

hammertounge

We may be leading you in the wrong direction (Or you may leading yourself in the wrong direction), but if I read the help file on UPDATE :
UPDATE - SQL can only update records in a single table.
This may mean that you cannot update a table by using a WHERE clause based on another table. And I tested this and I too cannot achieve the desired results. The only solution I found is to use the index on the ID field (I assume there is an index), for example:

Create Cursor myCursor1 (Id c(1),Name c(20),address c(10))
Insert Into myCursor1 (Id,Name) Values ("1","Mike")
Insert Into myCursor1 (Id,Name) Values ("2","Paul")
Insert Into myCursor1 (Id,Name) Values ("3","Frank")
Index On myCursor1.Id Tag Id
Set Order To Tag Id
Go Top
Create Cursor myCursor2 (Id c(1),address c(10))
Insert Into myCursor2 (Id,address) Values ("1","123")
Insert Into myCursor2 (Id,address) Values ("2","456")
Insert Into myCursor2 (Id,address) Values ("3","789")
Index On myCursor2.Id Tag Id
Set Order To Tag Id
Set Step On
Go Top
Select myCursor1
Do While !Eof()
	cId =""
	Store Alltrim(myCursor1.Id) To cId
	Select myCursor2
	If Seek( cId)
		Replace myCursor1.address With myCursor2.address
	Endif
	Select myCursor1
	Skip
Enddo
Select myCursor1
Browse

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.