Long old UPDATE statement

[biffta]

I have the following SQL which gives me the row Im after

 

SELECT *

FROM staff, staffemails

WHERE staff.users_username=\'$user\'

and staff.staff_id = staffemails.staff_id

 

thing is I now want to perform an update on the result of this query. Ive tried

 

UPDATE staffemails

SET is_registered =\'0\'

WHERE staff.users_username = \'$user\'

and staffemails.staff_id = staff.staff_id

 

But that doesnt work!

 

Any ideas????

[nabeel21]

hmmm.. only thing i can think of is that the \"is_registered\" column hold numbers only.. so try

 

UPDATE staffemails 

SET is_registered =0

WHERE staff.users_username = \'$user\' 

and staffemails.staff_id = staff.staff_id 

 

Lets see if it works this way....

 

--goodluck

[biffta]

No good sir. is-registered is a set so the quotes are needed!

 

The actual error MySql gives me is

Unknown table \'staff\' in where clause

which is definately not the case!

I believe the problem lies with the fact that I am using two tables and and update statement usually uses only one???

[gizmola]

With mysql updates are limited to a single row, or multiple columns if you specify a WHERE clause that includes a constant. Typically the way to do what you\'re trying to do would be something involving a subquery. I know that mysql is somewhat in the process of adding subqueries but I\'m not 100% current with version 4.x stuff now.

 

The query would be structured like this (if subqueries were working):

 

UPDATE staffemails

SET is_registered = \'0\'  (quotes not needed if is_registered is a numeric column type)

WHERE staff_id = (select staff_id from staff where username=\'$user\')

 

Alas they are not available to the best of my knowledge.

 

I do question your premise though. You state that this query gives you your \"row\".

 

SELECT *

FROM staff, staffemails

WHERE staff.users_username=\'$user\'

and staff.staff_id = staffemails.staff_id

 

 

It seems that all you are trying to do is shortcut your need to determine the user\'s staff_id. Simply select that first as I did in my subquery and do your simple update with the right staff_id in hand. It does take 2 queries, but they are short and should operate very quickly, especially if you are using the mysql_pconnect.

 

Even if you aren\'t mysql\'s connections are lightweight and pretty efficient.

[biffta]

I wanted to join the two tables via staff_id then do the update but as this is not possible the two query method will do me just as good!

 

Thanks

[jmabbate]

The MySQL syntax to update one table while joining to another is a bit different. It goes something like this:

 

UPDATE staffemails e, staff s

SET e.is_registered =\'0\'

WHERE s.users_username = \'$user\'

and e.staff_id = s.staff_id;

 

BTW, this works in MySQL 4.0.4 or later.

 

Joe