CODEWITHSUNDEEP
mysqlsql
Rpj
Rpj

Reputation: 6080

How to update a certain column value from values in the same row

foos table has the following columns (id, value)

bars tables has the following columns (id, foo_id, min, max, calc_value)

How to update calc_value to "select value from foo where id=foo_id" + "x" + min + "x" + max across all rows in the bars table

I am using the following function, but foo_id mapping is difficult

CONCAT_WS("x", "", min, max)

Upvotes: 0

Views: 133

Answers (1)

cartoonheart91
cartoonheart91

Reputation: 75

I don't get if "x + min + x + max" should be concatenated to value or to foo_id, in the first case:

update bars b
set calc_value = (select concat(value , "x", min, "x", max)
                  from foo f
                  where f.id = b.foo_id )

In the second case:

update bars b
    set calc_value = (select value 
                      from foo f
                      where f.id = concat(b.foo_id, x, min, x, max )

In the first case you manipulate the string in the select in order to set in calc_value the ready-to-go value. In the latter case you build up the key you want to match with foo.id.

Hope this helps!

Upvotes: 1

Related Questions