Jump to content


Photo

Long old UPDATE statement


  • Please log in to reply
5 replies to this topic

#1 biffta

biffta
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 29 December 2003 - 05:00 PM

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????

#2 nabeel21

nabeel21
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 29 December 2003 - 06:38 PM

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

UPDATE staffemails SET is_registered =0WHERE staff.users_username = \'$user\' and staffemails.staff_id = staff.staff_id

Lets see if it works this way....

--goodluck

#3 biffta

biffta
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 29 December 2003 - 06:51 PM

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???

#4 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 30 December 2003 - 12:14 AM

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 staffemailsSET 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.

#5 biffta

biffta
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 30 December 2003 - 12:29 AM

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 :D

#6 jmabbate

jmabbate
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 30 December 2003 - 02:05 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users