CODEWITHSUNDEEP
mysql
user1780464
user1780464

Reputation: 43

Applying SQL Query, working for one row, to all rows

I'm trying to remove part of a string, after a string is found, from a text field. What I want to remove will always be at the end of the string, will not be identical, but will always start with the same string, < iframe (no space between < and i, I just put it there because it won't print if I don't).

With the help of my brother-in-law, I was able to determine how to code it to run for one row, specified. But we now can't figure out how to run it for all rows. The code we have so far, for one row, is below:

update products_description  
set products_description 
        = (select test 
            from
            ( 
                select substr(PD.products_description,1,instr(PD.products_description, '<iframe') -1) as test  
                from products_description PD 
                where products_id=36
            ) as x)
where products_id=36

This is actually a followup to this thread, where I was unable to determine a solution.

Thanks to anyone that can shed some light on this.

EDIT: just thought to point out, the table and column I am dealing with are both named products_description.

Upvotes: 0

Views: 96

Answers (2)

Allan Ram&#237;rez
Allan Ram&#237;rez

Reputation: 2709

You have to make an update from select sentence

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

Could be something like:

update products_description as t 
join (select
            products_id,
            substr(PD.products_description,1,instr(PD.products_description, '<iframe') -1) as test
        from products_description PD
    ) as x 
on x.products_id = t.products_id
set t.products_description = x.test

Upvotes: 0

PinnyM
PinnyM

Reputation: 35531

Try it without the nested queries:

 UPDATE products_description
   SET products_description = SUBSTR(products_description,1,INSTR(products_description, '<iframe') -1)
   WHERE products_id IN (/* list of ids goes here */)

To run it for the whole table, simply leave out the WHERE clause.

Upvotes: 1

Related Questions