Re: How to traverse the rows of table, when a field meets condition then insert certain field value?

[Phillip Ward]

First things first:

Database != SpreadSheet

All the rows in a table have the same structure.
OK, yes, you can do weird stuff with JSON or XML data but they're inherently slower than doing the job properly.

Relational Databases work on "sets" of rows - you ask the database to "find" a set of rows and then to "do" something with that set. There is no "traversing" here, the database takes care of that for you.

So, to set values based on other values in the same row:

Quote

if `cname` have string in `[ 'dns1', 'dns2' ]`, then add the field `keyword` = `dns1`.

Assuming that means either "dns1" or "dns2", then:

update table1 
set keyword = 'dns1' 
where cname like '%dns1%' 
or    cname like '%dns2%' 
;

Quote

if `cname` have string in `[ 'dns3', 'cds1' ]`, then add the field `keyword` = `dns2`.

update table1 
set keyword = 'dns2' 
where cname like '%dns3%' 
or    cname like '%cds1%' 
;

Quote

if `cname` have string in `[ 'dns4' ]`, then add the field `keyword` = `NULL`.

update table1 
set keyword = NULL 
where cname like '%dns4%' 
;

Regards, Phill W.