sql statements

[Guest_imported]

Hi I wasn't sure which forum to post it in so I put it in here. The sql statements I need to make are for phpmyadmin.

Does anyone know an sql statement which deletes the "smilies/" from every record for the field = "icon" and table = "posts"?

Also does anyone know an sql statement which changes the subject for every record in the "posts" table with the subject from "threads"?. I tried the statement below but it didn't work:

UPDATE xmb2_posts set subject=SELECT subject FROM xmb2_threads WHERE 1

 

[KarveR]

1) delete icon from posts; <- will delete every record in the column 'icon' if you want only to delee a named set then :
delete icon from posts where icon = 'something_to_delete'
2) update xmb2_posts set subject = 'new_subject' where subject = 'old_subject_you_no_longer_want' ***************************************
Party on, dudes!

 

[AnakinPt]

update posts set icon=substr(icon,8) where substr(icon,0,8)='smilies/'

Try this query, but first check if he is going to update the right way with:

select icon,substr(icon,8) from posts where substr(icon,0,8)='smilies/'

This works in Oracle, in MySql, i don't know... Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[Guest_imported]

nope it didn't work, would you know how I can do it in mysql and the answer for the second bit was wrong aswell because what I need todo is something like:

UPDATE xmb2_posts set subject=SELECT subject FROM xmb2_threads WHERE 1

Thanx

 

[AnakinPt]

i think in mysql you cannot have nested queries.

and what do you want to update ? a select like that return a list of values and not a single one.
use parentisis to separate queries.

Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[KarveR]

Ah sorry , mis read the second one completely.

do the threads and posts have a common field like id?
If so you can try something like this:

UPDATE xmb2_posts set subject = xmb2_threads.subject WHERE xmb2_posts.id = xmb2_threads.id ***************************************
Party on, dudes!

 

[Guest_imported]

sup I tried UPDATE xmb_nfp_posts3 set subject = xmb_nfp_threads.subject WHERE xmb_nfp_posts3.tid = xmb_nfp_threads.tid but I got the error &quot;Unknown table 'xmb_nfp_threads' in where clause&quot; when the table xmb_nfp_threads does exist.

I'd be greatful for your help, Thanx