Theme

Welcome · log in · join

Web Developers → MySQL syntax help

uniqs
497

« Microsoft All-In-One Code Framework • newbie php question »

TearAbite D'oh join:2001-07-25 Rancho Cucamonga, CA	TearAbite Member 2010-Nov-23 3:43 pm MySQL syntax help I am trying to update all the records in a table that contains a string LIKE something.. So.. if this pulls the records i want to change: SELECT * FROM `pages` WHERE `body` LIKE '%Joe%' how would i do the equivalent of a REPLACE like this: update pages set body = replace(body, "Joe", "Ralph" what i want to do is change everything that contains "joe" (joe, joeseph, joeclyn) to something else..
	· actions · 2010-Nov-23 3:43 pm ·

JAAulde Web Developer MVM join:2001-05-09 Frederick, MD	JAAulde MVM 2010-Nov-23 3:49 pm Do you need to set them all to the same value?
	· actions · 2010-Nov-23 3:49 pm ·
TearAbite D'oh join:2001-07-25 Rancho Cucamonga, CA	TearAbite Member 2010-Nov-23 3:49 pm yes - the new value will be static
	· actions · 2010-Nov-23 3:49 pm ·
JAAulde Web Developer MVM join:2001-05-09 Frederick, MD	JAAulde to TearAbite MVM 2010-Nov-23 3:52 pm to TearAbite How about something like this? UPDATE `table` SET `column` = 'value' WHERE `id` IN ( SELECT `id` FROM `table` WHERE `column` LIKE '%Joe%' );
	· actions · 2010-Nov-23 3:52 pm ·
TearAbite D'oh join:2001-07-25 Rancho Cucamonga, CA	TearAbite Member 2010-Nov-23 3:56 pm ok, so i know my table, and the column.. and the value (my new value, right?) but what is "id" ?
	· actions · 2010-Nov-23 3:56 pm ·
JAAulde Web Developer MVM join:2001-05-09 Frederick, MD	JAAulde to TearAbite MVM 2010-Nov-23 3:59 pm to TearAbite The column which represents your table's primary key.
	· actions · 2010-Nov-23 3:59 pm ·
TearAbite D'oh join:2001-07-25 Rancho Cucamonga, CA	TearAbite Member 2010-Nov-23 4:07 pm ok.. but this is what i get: #1093 - You can't specify target table 'node_revisions' for update in FROM clause this is the actual line of code: UPDATE `node_revisions` SET `teaser` = '/files' WHERE `vid` IN ( SELECT `vid` FROM `node_revisions` WHERE `teaser` LIKE '%/system/files%' );
	· actions · 2010-Nov-23 4:07 pm ·
JAAulde Web Developer MVM join:2001-05-09 Frederick, MD	JAAulde to TearAbite MVM 2010-Nov-23 4:12 pm to TearAbite Bummer...my bad. I thought for sure that would work, but I just tested and got the same error.
	· actions · 2010-Nov-23 4:12 pm ·
TearAbite D'oh join:2001-07-25 Rancho Cucamonga, CA	TearAbite Member 2010-Nov-23 4:12 pm any other options/ideas? is this even possible?
	· actions · 2010-Nov-23 4:12 pm ·
JAAulde Web Developer MVM join:2001-05-09 Frederick, MD	JAAulde MVM 2010-Nov-23 4:19 pm UPDATE node_revisions SET teaser = replace( teaser, teaser, '/files' ) WHERE teaser LIKE '%/system/files%';
	· actions · 2010-Nov-23 4:19 pm ·
TearAbite D'oh join:2001-07-25 Rancho Cucamonga, CA	TearAbite Member 2010-Nov-23 4:27 pm ok.. and now i feel a bit (more) like a fool.. but when i said the replacement was 'static', i meant that the replacement string would always be the same- so if before 'teaser'="this is the full path /system/files" the after should be "this is the full path /files" but the above changes the entire teaser field to '/files'
	· actions · 2010-Nov-23 4:27 pm ·
JAAulde Web Developer MVM join:2001-05-09 Frederick, MD	JAAulde MVM 2010-Nov-23 4:29 pm Ah, yes, that is why I asked. I don't know how to do it dynamically off the top of my head. It will take a decent bit of googling. As you can tell from my originally leading you astray, I am not terribly skilled in the DB world.
	· actions · 2010-Nov-23 4:29 pm ·
TearAbite D'oh join:2001-07-25 Rancho Cucamonga, CA	TearAbite Member 2010-Nov-23 4:45 pm looks like this will work update node_revisions set teaser = replace(teaser, '/system/files', '/files')
	· actions · 2010-Nov-23 4:45 pm ·
JAAulde Web Developer MVM join:2001-05-09 Frederick, MD	JAAulde MVM 2010-Nov-23 5:36 pm Ah, well, that's simple enough. Glad you got it!
	· actions · 2010-Nov-23 5:36 pm ·
TearAbite D'oh join:2001-07-25 Rancho Cucamonga, CA	TearAbite Member 2010-Nov-23 8:46 pm said by JAAulde: Ah, well, that's simple enough. Glad you got it! took me a while with Google and you got me started in the right direction.. thanks for your help..
	· actions · 2010-Nov-23 8:46 pm ·

Forums → Software and Operating Systems → Web Developers	« Microsoft All-In-One Code Framework • newbie php question »