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` = '0000-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

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` = '0000-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

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 <2wsxdr5@cox.net> 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` = '0000-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/mysql?unsub=anandkl@gmail.com
>
>