mysql: how to perform these 2 tasks

Question

hi,

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 ?

thanks

Answer 1

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:

CREATE VIEW Docs_view AS
SELECT Docs.*, Users.Username
FROM Docs
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.

Answer 2

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.

DELIMITER //

CREATE TRIGGER Docs_after_insert_trigger AFTER INSERT ON Docs
FOR EACH ROW
BEGIN
    IF ( COALESCE(@DISABLE_TRIGGERS, 0)  1 ) THEN
        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.