Jay-Jay Jay-Jay - 1 year ago 90
SQL Question

How to update column if another column fulfill specific criteria?

I need to update one column if another column has a specific data.

Usually if I want to update one column, I do the following SQL Query:

UPDATE table1
SET field1 = replace(field1, 'oldstring', 'newstring')

But what I can't figure is how to make it look up one column, and if that field has some data, it should update another column's field.

Here is what I want to do.

  1. look in table: phpbb_tree

  2. under column: spouses_total

  3. if the field is empty (has no data)

  4. update column: page_template

  5. update from: tree_body_spouse_1.html to: tree_body_single.html

So basically, I know how to do the "update" part, but don't know how to make it look first in one column, and if empty (or matches) it should do the following:

UPDATE phpbb_tree
SET page_template = replace(page_template, 'tree_body_spouse_1.html', 'tree_body_single.html')

Hopefully someone could tell me how to write it up. I don't even know if it's even possible to do a search for an empty data in a column?

Answer Source

You could use CASE expression to fulfill different condition of replacement.

UPDATE phpbb_tree
SET  page_template = (CASE  
                        WHEN spouses_total is null  
                          THEN replace(page_template, 'tree_body_spouse_1.htm', 'tree_body_single.html')            
                        ELSE page_template


Please check this..


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download