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

Question

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.

Ok, pretty weird query, didn't really inspect it, but maybe if you remove the last where part (where products_id=36) it will do it for all the rows? Make a backup to be sure of course. — gitaarik, Nov 07 '12 at 22:19

score 1 · Accepted Answer · answered Nov 07 '12 at 22:19

1

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.

answered Nov 07 '12 at 22:19

PinnyM

35,165
3
73
81

Thank you for this. It got us there! We originally tried `UPDATE products_description SET products_description = SUBSTR(products_description,1,INSTR(products_description, ' – user1780464 Nov 08 '12 at 18:51
So I changed it to `UPDATE products_description SET products_description = SUBSTR(products_description,1,INSTR(products_description, ' – user1780464 Nov 08 '12 at 18:51
you can simplify this further (again no subquery) if you just use `WHERE products_description like '% – PinnyM Nov 08 '12 at 18:53

Allan Ramírez · Answer 2 · 2012-11-08T16:32:08.077

0

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

edited Nov 08 '12 at 16:32

answered Nov 07 '12 at 22:30

Allan Ramírez

2,689
2
17
15

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

2 Answers2