CODEWITHSUNDEEP
mysqlsqlphpmyadminmariadb
cortvi
cortvi

Reputation: 133

SQL insert modified value from one field to a new one

my problem here is that I've got no idea of SQL queries but need to do something I'm assuming should not be hard. I've tried some of the recommended questions but I just can't really understand how to extrapolate it to my case, hope you can help me.

Basically what I need is to create some rows in a table, and populate their fields with the values from other rows in the same table, I'll try to be very specific.

I've a got a table ( vb_postmeta ) with a lot of rows, some of them look like this:

meta_id post_id meta_key meta_value
1434 500 bpxl_videourl lOO6hyOKoUM
1435 501 bpxl_videourl skjd9F008mN

What I need here is, for each of these rows, to create a new one that look like this:

meta_id post_id meta_key meta_value
AUTO 500 csco_post_video_url https://www.youtube.com/watch?v=lOO6hyOKoUM
AUTO 501 csco_post_video_url https://www.youtube.com/watch?v=skjd9F008mN

meta_id is the key, so should auto-generate, while post_id should be the same and meta_value should also have the same value with the Youtube URL preppended.

I don't really know how hard is this but hope not too much,
thanks in advance for the help, been struggling a lot with this.

Upvotes: 0

Views: 290

Answers (3)

cortvi
cortvi

Reputation: 133

Following suggested answers I ended up using this:

INSERT INTO vb_postmeta(
    SELECT
        NULL,
        post_id,
        'csco_post_video_url',
        CONCAT('https://www.youtube.com/watch?v=', meta_value)
    FROM
        vb_postmeta
    WHERE
        meta_key = 'bpxl_videourl'
)

Upvotes: 1

Rooba
Rooba

Reputation: 16

Based on your question, I am assuming you need to insert new rows into the same table - vb_postmeta .

For this,

INSERT INTO vb_postmeta (post_id,meta_key,meta_value) SELECT post_id,meta_key,''meta_value 'https://www.youtube.com/watch?v='+NAME FROM vb_postmeta

Upvotes: 0

Alison Wasserfall
Alison Wasserfall

Reputation: 19

Assuming your meta_id is AI(auto increment) and using mySQL(minor change for MSSQL), try:

INSERT INTO vb_postmeta (
SELECT null, 
post_id, 
'csco_post_video_url' 
CONCAT('https://www.youtube.com/watch?v=',meta_value
FROM vb_postmeta)

Upvotes: 0

Related Questions