Deleting a list of things

[olmops]

Hi there

unfortunately I could not get helpful search results for my problem. Maybe one of you knows an answer:

I have 2 tables.

Table 1 AllMyData
Primary key consists of more than one column
e.g. column A and column B

Table 2 MySelection
a subset of keys from Table 1
column A, column B

I need to UPDATE a field of table 1 in all rows whose keys appear in Table 2

If there was only one primary key, I would do:

UPDATE AllMyData.X SET X=false
WHERE AllMyData.A IN (SELECT A FROM MySelection)

(basically I need this when A and B together are the primary key - but all I tried has failed so far)

 

[HarleyQuinn]

Something along the lines of:

UPDATE AllMyData.X SET X=false
WHERE AllMyData.A & AllMyData.B IN (SELECT A & B FROM MySelection)

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[olmops]

I have tried that.
Access accepts the query, but the execution takes like forever (99% CPU load, I waited for several minutes, but the progress bar doesnt even move).
Table 1 has 60k rows, Table 2 has 700 rows. The query to create Table 2 takes about 1-2 seconds to execute.

 

[HarleyQuinn]

If this is a one off you could add a new column to each table which contains the composite key value so you aren't doing string manipulation on the fly in the query. This should dramatically speed up the query (for 80,000 in table1 and 1100 in table2 this variation took less than a second to execute).

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[olmops]

Yes, thanks a lot!

I dislike the idea and I really ask myself what Access really does... but it works.
I have added a separate step before that adds the composite key to the main table before.

 

[HarleyQuinn]

As you say, it's not ideal but...

Glad you got it to work in an acceptable time

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[PHV]

Did you try this ?

UPDATE AllMyData INNER JOIN MySelection
ON AllMyData.A = MySelection.A AND AllMyData.B = MySelection.B
SET AllMyData.X = False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886