Re: update select question

2008-04-15 Thread Sebastian Mendel 

Chris W schrieb:

I have the following query...

SELECT c.NLCID, n.publishdate
FROM newsletter n
JOIN newslettersection s using (NLID)
JOIN newslettercontent c using(NLCID)
WHERE contenttype = 1 AND n.publishdate AND c.`timestamp` = '-00-00 
00:00:00'


I want to run an update on newslettercontent and set its timestamp 
column to be the publishdate from  the newsletter table using the join 
rules in that query.  Is there a way to do that in a query?


yes, you can, see multi-table update

http://dev.mysql.com/doc/refman/5.0/en/update.html

--
Sebastian

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

Re: update select question

2008-04-15 Thread Ananda Kumar 
update newslettercontent c set c.timestamp= (select n.publishdate  from
newsletter n where
n.NLCID= c.NLCID);

This should work.


On 4/16/08, Chris W [EMAIL PROTECTED] wrote:

 I have the following query...

 SELECT c.NLCID, n.publishdate
 FROM newsletter n
 JOIN newslettersection s using (NLID)
 JOIN newslettercontent c using(NLCID)
 WHERE contenttype = 1 AND n.publishdate AND c.`timestamp` = '-00-00
 00:00:00'

 I want to run an update on newslettercontent and set its timestamp column
 to be the publishdate from  the newsletter table using the join rules in
 that query.  Is there a way to do that in a query?  I should probably just
 write a quick script to do it since I could probably do that in less time
 than I have spent trying to figure out a query to do it.


 --
 Chris W
 KE5GIX

 Protect your digital freedom and privacy, eliminate DRM, learn more at
 http://www.defectivebydesign.org/what_is_drm;

 Ham Radio Repeater Database.
 http://hrrdb.com


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

an update select question

2003-11-05 Thread Jason Joines 
  I have a table called employees 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: an update select question

2003-11-05 Thread gerald_clark 
update employees set userid=substring(.

Jason Joines wrote:

  I have a table called employees 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: an update select question

2003-11-05 Thread Chris 
IF I understand you correctly, you should be able to do this:

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

Chris

-Original Message-
From: news [mailto:[EMAIL PROTECTED] Behalf Of Jason Joines
Sent: Wednesday, November 05, 2003 9:14 AM
To: [EMAIL PROTECTED]
Subject: an update select question


   I have a table called employees 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]


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

Re: an update select question

2003-11-05 Thread Jason Joines 
gerald_clark wrote:
update employees set userid=substring(.

Jason Joines wrote:

  I have a table called employees 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
===


Thank you.

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

is exactly what I needed.

Jason
===


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