Update not working when comparing fields in two tables 1

[jerijeri]

Hi,

I have a master customer table. I also have a temporary table for customers who meet a set of criteria each month. Our plan was to upload the acct numbers into the temporary table and then modify the master table if the customer's ID is in the temporary table.

Here's the query.

UPDATE customer SET customer.actnum = '1', customer.bankact = '' WHERE customer.custID = customer_tmp.custID

also tried:

UPDATE customer SET actnum = '1', bankact = '' WHERE customer.custID = customer_tmp.custID

error message:

Unknown table 'customer_tmp' in where clause

now the customer_tmp table does exist. I'm not sure why this query isn't working.

Thanks,

Jer

 

[swampBoogie]

>>I'm not sure why this query isn't working.

Well, the SQL support in MYSQL is very limited. Normally a query like this would be written as

UPDATE customer SET customer.actnum = '1', customer.bankact = '' WHERE customer.custID in (select custId from customer_tmp )

but this is not supported in Mysql. Maybe it will be implemented in the next version.

If you continue to use MYSQL I currently only see such ugly solutions as reading the data (i.e. the data used to populate the temp table) to the client and build the update statement dynamically.

 

[jerijeri]

Thanks for the help.

I just discovered this:

[Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;]

I must say I'm shocked that this wasn't available before this. This seemed to be such a basic feature. We've got a workaround where we select all fields and create a temporary table. Then we delete the extra field. Then the original table is renamed, and the temporary table is renamed to have the original table name.

Like you said - ugly.

Thanks.

Jer