Using select result to replace string

Question

I have a table in which I need to concatenate values from verse table to chapter table. This should be done only when the verse field value starts with a caret('^'). They both share entity_id column.

I made the following select query, and got the values I need :

SELECT 
chapter.field_bible_chapter_value,
verse.field_bible_verse_value,
verse.entity_id
FROM field_data_field_bible_verse AS verse
INNER JOIN field_data_field_bible_chapter AS chapter
ON chapter.entity_id = verse.entity_id
WHERE verse.field_bible_verse_value LIKE '^%'
ORDER BY verse.entity_id

Now I am stuck trying to use this data to make actual changes to the chapter field.

How should I approach this?

sry, I thought it was for T-sql (not mysql). In T-SQL we can save this query as a temporary expression so when you execute a larger piece of code that uses CTE/derived column(expression), it will use the output of this query and perform the other data processing using this data. — 007
– 007, Commented Jul 18, 2013 at 16:02

sel · Accepted Answer · 2013-07-19 04:03:38Z

1

If i understand the requirement correctly, please try the below sql:

UPDATE field_data_field_bible_verse AS verse
INNER JOIN field_data_field_bible_chapter AS chapter
ON chapter.entity_id = verse.entity_id
SET chapter.field_bible_chapter_value = concat(verse.field_bible_verse_value,' ',chapter.field_bible_chapter_value)
WHERE verse.field_bible_verse_value LIKE '^%'

The logic is as below:

IF entity_id is equal THEN
    IF the verse_value starts with '^' THEN
       Concatenate verse value with chapter value
       Replace chapter value with the concatenated value

answered Jul 19, 2013 at 4:03

sel

4,9421 gold badge18 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Skarlinski Over a year ago

Thanks a lot. So actually there is no need to use select? The update can retrieve data as a select would?

sel Over a year ago

Yes. No need to use select. There is syntax can be used in your scenario.

obecker · Accepted Answer · 2013-07-18 16:29:09Z

0

It seems you want a multi-table update. The syntax is pretty straight forward, see http://dev.mysql.com/doc/refman/5.0/en/update.html

For your example this will be something like

UPDATE field_data_field_bible_verse AS verse
INNER JOIN field_data_field_bible_chapter AS chapter
ON chapter.entity_id = verse.entity_id
SET chapter.TARGET_COLUMN = concat(YOUR_VALUES)
WHERE verse.field_bible_verse_value LIKE '^%'

(you have to fill the SET line with the correct names and values)

answered Jul 18, 2013 at 16:29

obecker

2,4172 gold badges22 silver badges25 bronze badges

2 Comments

Skarlinski Over a year ago

Thanks. The problem is that in the place you put 'YOUR_VALUES', I need to use values acquired by a select. Each line needs to concat different values - the particular chapter needs it's particular verse. In the 'WHERE' clause, there should be the entity_id, and in the SET clause the concated values unique to it

obecker Over a year ago

Without some example data it's hard to understand your use case. Probably an UPDATE with a subquery will solve your problem. Take a look at the second to last example on dev.mysql.com/doc/refman/5.0/en/subqueries.html

Collectives™ on Stack Overflow

Using select result to replace string

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related