Re: another insert select question

2003-11-05 Thread Egor Egorov 
Jason Joines [EMAIL PROTECTED] wrote:
 Table employees:
 
  
 | idnumber | email | phone | address |
  
 
 Table webusers:
  -
 | idnumber | userid | website |
  -
 
   Table employees is completely populated.  Table webusers has the 
 idnumber and website fields completely populated.  What query can I use 
 to insert the email address from each persones record in the employees 
 table into the userid field of their corresponding record in the 
 webusers table?

If I've got you right you need UPDATE, not INSERT .. SELECT.

UPDATE webuser, employees
SET webuser.userid=employees.email
WHERE webuser.idnumber=employees.idnumber;

The above UPDATE statement will work from v4.0.4.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: another insert select question

2003-11-05 Thread Jason Joines 
Egor Egorov wrote:
Jason Joines [EMAIL PROTECTED] wrote:

Table employees:


| idnumber | email | phone | address |

Table webusers:
-
| idnumber | userid | website |
-
 Table employees is completely populated.  Table webusers has the 
idnumber and website fields completely populated.  What query can I use 
to insert the email address from each persones record in the employees 
table into the userid field of their corresponding record in the 
webusers table?


If I've got you right you need UPDATE, not INSERT .. SELECT.

UPDATE webuser, employees
SET webuser.userid=employees.email
WHERE webuser.idnumber=employees.idnumber;
The above UPDATE statement will work from v4.0.4.
  You've got it right, I was way off trying to use an INSERT instead of 
an UPDATE.  These tables were an example where I was trying to get the 
syntax right before using it on the real thing.  I thought I had to use 
two tables to make it work.
  The real thing is one table called on a 3.23.48 server.  One of it's 
fields is an email address (email) and one is the userid.  The primary 
key is idnumber.  I need to populate the userid field from the email 
address field.  I can get the userid using:

SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) 
from employees;

but I haven't been able to get the results into the userid field.

  Any ideas?

Thanks,

Jason
===


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: another insert select question

2003-11-05 Thread Jason Joines 
Jason Joines wrote:
Egor Egorov wrote:

Jason Joines [EMAIL PROTECTED] wrote:

Table employees:


| idnumber | email | phone | address |

Table webusers:
-
| idnumber | userid | website |
-
 Table employees is completely populated.  Table webusers has the 
idnumber and website fields completely populated.  What query can I 
use to insert the email address from each persones record in the 
employees table into the userid field of their corresponding record 
in the webusers table?


If I've got you right you need UPDATE, not INSERT .. SELECT.

UPDATE webuser, employees
SET webuser.userid=employees.email
WHERE webuser.idnumber=employees.idnumber;
The above UPDATE statement will work from v4.0.4.


  You've got it right, I was way off trying to use an INSERT instead of 
an UPDATE.  These tables were an example where I was trying to get the 
syntax right before using it on the real thing.  I thought I had to use 
two tables to make it work.
  The real thing is one table called on a 3.23.48 server.  One of it's 
fields is an email address (email) and one is the userid.  The primary 
key is idnumber.  I need to populate the userid field from the email 
address field.  I can get the userid using:

SELECT substring(per_email_address,1,instr(per_email_address,'@')-1) 
from employees;

but I haven't been able to get the results into the userid field.

  Any ideas?

Thanks,

Jason
===






Turns out I need UPDATE SET

UPDATE employees SET userid=substring(email,1,instr(email,'@')-1);

Solution provided to me in the an update select question thread.

Jason
===


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

another insert select question

2003-11-04 Thread Jason Joines 
Table employees:

 
| idnumber | email | phone | address |
 
Table webusers:
 -
| idnumber | userid | website |
 -
  Table employees is completely populated.  Table webusers has the 
idnumber and website fields completely populated.  What query can I use 
to insert the email address from each persones record in the employees 
table into the userid field of their corresponding record in the 
webusers table?

Thanks,

Jason Joines
Open Source = Open Mind

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]