CODEWITHSUNDEEP
mysql
achuth
achuth

Reputation: 1212

Insert a field value from another table dynamically

It's a newbie question on mysql, I have the following two tables in same database

       Users                                    vehicles

User   Level   Group                Vehicle    User    GroupID
 A       0       AG                   x         A         x
 a1      1       AG                   y         a2        x  
 a2      1       AG                   z         a1        x
 B       0       BG                   
 b1      1       BG
 b2      1       BG

User.user having Level = 0 is a superUser(Only one superUSer per Group).

In vehicles User is a Fk on Users.user, now needed to create a new column GroupID. Here i got into the problem, i need to set the GroupID value to Users.User who is superUser of Vehicles.User.

Finally the Vehicles table should look like

  Vehicle    User    GroupID
    x         A         A
    y         a2        A  
    z         a1        A

Any sample query to do this for the entire Vehicles table will be helpful for me, Thanks.

Upvotes: 0

Views: 96

Answers (3)

Aret
Aret

Reputation: 475

You can update the table like this, for the new column.

UPDATE
    vehicles v
SET
    v.GroupID = (SELECT User FROM Users WHERE Level = 0 AND Group IN (SELECT Group FROM Users WHERE User = v.User));

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You need to use the user defined variables to to fill up your vehicles table according to their relevant group 

UPDATE `vehicles` v
JOIN (
SELECT 
`User` ,
@g:=`Level`,
@u:= CASE WHEN @g= 0 THEN `User` ELSE @u END `new_group`
FROM users 
JOIN(SELECT @g:=-1 ,@u:='') t
ORDER BY `User` ,`Level`
) n
ON(n.`User` = v.`User`)
SET v.GroupID = n.new_group

Upvotes: 1

Avinash Kalola
Avinash Kalola

Reputation: 113

    UPDATE
        user.vehicles
    SET
        user.vehicles.GroupID = user.user.User
    FROM
        user.vehicles
    INNER JOIN
        (SELECT User FROM user.user WHERE Level = 0) user.user

     ON
    user.user.User = user.vehicles.User

OR 

UPDATE
    user.vehicles
SET
    user.vehicles.GroupID = (SELECT User FROM (SELECT User FROM user.user WHERE Level = 0))

Try Above Query I Hope Help You........!!

Upvotes: 0

Related Questions