CODEWITHSUNDEEP
mysqlselectsql-update
Tyssen
Tyssen

Reputation: 1711

MySQL update from another select

I need to update a column based on the values from another table.

This select statement produces the results I need

select ct.entry_id, ct.url_title, ct.title
from exp_channel_titles ct, exp_channel_data cd
where ct.channel_id = 1
and cd.channel_id = 2
and ct.title = cd.field_id_2
and ct.status = 'open'

but the column that I'm inserting into takes data in this format:

[entry_id] [url_title] title

so my insert statement would look like

update exp_channel_data
set field_id_1 = ([1234] [page-title] Page Title)
where...

so how do I format my select statement so that it outputs the data with the [] and spaces?

Upvotes: 1

Views: 124

Answers (2)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

You can use INNER JOIN within UPDATE queries, giving something like

UPDATE exp_channel_data cd
INNER JOIN exp_channel_titles ct
    ON ct.title = cd.field_id_2
    AND ct.channel_id = 1 
    AND ct.status = 'open'

SET cd.field_id_1 = CONCAT('[',ct.entry_id,'] [',ct.url_title,'] ',ct.title)

WHERE cd.channel_id = 2

Upvotes: 2

John Woo
John Woo

Reputation: 263693

use CONCAT

SELECT CONCAT('[', ct.entry_id, '] [', ct.url_title, '] ', ct.title)

Upvotes: 1

Related Questions