Take the 2-minute tour ×

how can I do the following points in mysql (possibly using phpmyadmin):

1) Given an existing column of numeric values (colA), create a new column with text data (colB) and automatically fill this column with "typeA" if colA = 0, and "typeB" if colB > 0 ?

2) Given the following 2 tables Users and Docs:

Table Users has the columns "Username" and "UserID", Table Docs has columns "DocID", "Username" and UserID" (it is redundant, I know)

The column Username in table Docs is empty and I have to fill it automatically given the UserID.

What's the SQL code for these 2 tasks ?


share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

For the first one, you can do:

UPDATE table_1 SET colB=IF(colA=0, 'typeA', IF(colA>0, 'typeB', '???'));

I don't know if it is ok how you specify in your question "and "typeB" if colB > 0", because colB is the column where you want to write.

For your second question, you should use a view to have the username in table Docs. To update it anyway, you can use the following:

UPDATE Docs d SET Username=(SELECT Username FROM Users u WHERE u.UserID=d.UserId)

Here is how to create a view that contains the Username:

SELECT Docs.*, Users.Username
LEFT JOIN Users ON Docs.UserID=Users.UserID;

First drop the column Username from Docs.

Now, when you want to select the documents, you can see the Usernames too:

SELECT * FROM Docs_view

And when you add a new record, you only have to specify UserID.

share|improve this answer
That's what I was wondering, did he mean colA > 0 ? –  rlb.usa Jul 30 '10 at 17:02
yes I meant that, sorry, thanks! –  Patrick Jul 31 '10 at 18:32
add comment

For the first one, I think that True Soft's answer should work.

For the second question, you probably want to use triggers to maintain the denormalized Username field.


CREATE TRIGGER Docs_after_insert_trigger AFTER INSERT ON Docs
        UPDATE Docs 
            SET Username = ( SELECT Username FROM Users where UserID = new.UserID ) 
            WHERE DocID = new.DocID;
    END IF;
END //

Create a similar trigger for 'after update' changing all 'insert' to 'update' if the Docs will ever change UserID and Username, so that they'll be updated to stay synchronized.

share|improve this answer
add comment

Your Answer


