MySQL Forums
Forum List  »  Newbie

Re: How to traverse the rows of table, when a field meets condition then insert certain field value?
Posted by: Phillip Ward
Date: November 26, 2021 04:22AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: How to traverse the rows of table, when a field meets condition then insert certain field value?
November 26, 2021 04:22AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.