Complicated INSERT Statment 2

[3112005]

I have a table called USERS that has a column called Division. The values are actually the Division_id from another table called DIVISIONS. I need to create a column in the USERS table that lists the Division_name, which can also be found in the DIVISION table.

Each Division_name has a Division_id, so the new column's data (Division_name) must equal the Division_id that is already in the USER table.

I'm not sure if this can be done, but I think it needs to be something like this....

INSERT INTO users (sbu)
From Division d, Users u
WHERE u.division_id = d.division_name

I'm really new at SQL so I need some assistance.

 

[Golom]

Its usually not recommended that you duplicate data in different locations in your tables because of the problems associated with keeping them synchronized. The preferred solution is to join the tables and generate a VIEW that presents the fields that you want as in

Select U.UserID, U.Division_ID, D.Division_Name

From Users U INNER JOIN Divisions D
     ON U.Division_id = D.Division_id

However, if you must

UPDATE Users U INNER JOIN Divisions D
       ON U.Division_id = D.Division_id

SET U.Division_Name = D.Division_Name

Assuming that you have already created a Division_Name field in the USERS table.

 

[3112005]

I think I see what you mean about using a view, but in this case I dont' think I can. The purpose of having this additional column for the Division_Name is so that I can create a DB driving Org. Chart from the USER table. The software I found that can do this does not allow you to do table joins, but only pull from one table.

I tried to do the UPDATE Clause you wrote, but I received an error saying...

Error in table name or view name in UPDATE clause.
Unable to parse query text.

 

[PHV]

And this ?
UPDATE Users
SET Division_Name = (SELECT Division_Name FROM Divisions WHERE Division_id = Users.Division_id)
WHERE Division_id IS NOT NULL
AND Division_Name IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3112005]

I get the error...

String or binary data would be truncated.

 

[PHV]

Are you saying that the Users.Division_Name and Divisions.Division_Name columns aren't the same type ?

 

[Golom]

As PHV said ... are they not of the same type and/or the same size?

As to ...but only pull from one table

Most database systems allow you to treat views (or queries) as though they were tables. If you store the VIEW and then base your Org Chart on the view, the Org Chart software shouldn't know the difference.

 

[3112005]

The columns were not the same type, so I fixed that and was able to update the Division_Name column.

I forgot that a view is treated as a table. That is what I should do in order to stick with good practice.

Thank you so much (both of you) for you help!