MySql, Copy one column into another and add some text

I have a column which has an Article-ID and one empty with the picture filename. Example: The article with the ID 34.67 should get the filename 34.67.jpg and so on. There are about 20'000 articles.

Screenshot

What would be the best way to do this?

Welcome to Stack Overflow. Questions that seek answers based on opinions will be closed. Please see stackoverflow.com/help/dont-ask for more information. You can edit the question so that it isn't seeking an opinion based answer.
– Jason Aller
Aug 21 at 22:13

2 Answers
2

Depending on your table structure or SQL dialect - it will be something like this:

UPDATE Tablename SET filename=concat(articleid, '.jpg')

You could test it in advance using SELECT concat(articleid, '.jpg') FROM Tablename
– Andre Albert
Aug 21 at 8:35

SELECT concat(articleid, '.jpg') FROM Tablename

So I edit my question and added a screenshot. If I understnad correctly the command would be UPDATE oxarticles SET OXPIC1=concat(OXARTNUM, '.jpg') ? Correct?
– Sebastian Krug
Aug 21 at 8:53

So you could try SELECT concat(OXARTNUM, '.jpg') FROM OXARTICLES first
– Andre Albert
Aug 21 at 8:57

SELECT concat(OXARTNUM, '.jpg') FROM OXARTICLES

This would work. Can I only insert the values in columns which are empty?
– Sebastian Krug
Aug 21 at 9:13

then it would be something like: UPDATE oxarticles SET oxpic1=CONCAT(oxartnum, '.jpg') WHERE oxpic1 IS NULL
– Andre Albert
Aug 21 at 9:17

UPDATE oxarticles SET oxpic1=CONCAT(oxartnum, '.jpg') WHERE oxpic1 IS NULL

OK, Thank you very much. I ended up using following code. This worked like a charm.

UPDATE oxarticles SET OXPIC1=LOWER(concat(OXARTNUM, '.jpg')) WHERE OXPIC1 = ' '

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt