Update one table's column, based off another table's column where they have matching IDs

Question

Alright, so I currently have two tables where they have corresponding IDs (it's how we know which listing belongs to which user, they have the same ID). Basically, I want to update all the usernames (column "login") of one table (pmd_users) based off the website URL (column "www") of the other table (pmd_listings), matching them based off IDs. Except, with the http:// removed.

So, the table pmd_users has the field "id" and pmd_listings has "user_id". We only need to update the column "login" in the table "pmd_users". I was thinking of doing it like below, except I'm not sure that's correct, and I have no idea how I would remove "http://" and "https://" from the website (www) column.

UPDATE pmd_users
       INNER JOIN pmd_listings 
          ON pmd_listings.user_id = pmd_users.id
SET    pmd_users.login = pmd_listings.www

Any advice?

TL;DR I want the table pmd_users column "login" to match the table pmd_listings column "www" where their ID columns match, except without http://.

Possible duplicate of [MySQL string replace](https://stackoverflow.com/questions/5956993/mysql-string-replace) — fubar, Apr 09 '19 at 22:34
Oh, I didn't think about that. However, I'd have to copy the data over, and then run a separate query to remove the unwanted bits, rather than doing it at the same time? As I don't want the http removed from the origianl column, only on the copies in the new table. This could work though. — Natsu, Apr 09 '19 at 22:46
no you could just `SET pmd.user.login = REPLACE(...)`. The source column would be unaffected. — fubar, Apr 10 '19 at 02:45

JayCo741 · Accepted Answer · 2019-04-09T23:41:59.313

1

I think you can do this with substr() and case.

UPDATE pmd_users INNER JOIN pmd_listings
    ON pmd_listings.user_id=pmd_users.id
SET pmd_users.login = CASE
    WHEN substr(pmd_listings.www, 1,7) = 'http://'
        THEN substr(pmd_listings.www, 8)
    WHEN substr(pmd_listings.www, 1,8) = 'https://'
        THEN substr(pmd_listings.www, 9)
    ELSE pmd_users.login
END;

edited Apr 09 '19 at 23:41

answered Apr 09 '19 at 22:47

JayCo741

409
4
14

Willing to give it a go (with backup) but finding this error "MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE' at line 9" – Natsu Apr 09 '19 at 23:04
Syntax works when I use `END` instead of `END CASE`. Edited my answer. – JayCo741 Apr 09 '19 at 23:41
Thank you! This works, if you have a second, do you mind explaining what the "1,7" "8" "1,8" "9" mean? I'm not quite sure. Only if you have time and care to, otherwise thanks so much! – Natsu Apr 10 '19 at 00:30
The 2 values after the string refer to the beginning and ending index numbers of the string. The ending index number is optional if you want to return everything to the right of the first index. – JayCo741 Apr 10 '19 at 15:19

score 0 · Answer 2 · answered Apr 09 '19 at 23:17

Well, it's not all in one, unless someone can help me with that I can achieve the result I want using two queries as followed:

UPDATE pmd_users
       INNER JOIN pmd_listings 
          ON pmd_listings.user_id = pmd_users.id AND pmd_listings.www != ""
SET    pmd_users.login = pmd_listings.www

*Added addition to remove empty columns, and then once that is done use:

UPDATE pmd_users
SET login = REPLACE(login, 'http://www', '')
WHERE login LIKE 'http://www.%'

To remove the extra from the login.

It works enough! Thanks everyone.

`UPDATE pmd_users INNER JOIN pmd_listings ON pmd_listings.user_id = pmd_users.id AND pmd_listings.www <> '' SET pmd_users.login = REPLACE(pmd_listings.www, 'http://', '');` — fubar, Apr 10 '19 at 02:47

Update one table's column, based off another table's column where they have matching IDs

2 Answers2