Not sure about subquery or join

[dharkangel]

Hi,

I have two tables, Table1 has two columns called Username, Role. Table2 has two columns called Assigned_To and Role. I am trying to do something like this:

INSERT INTO Table2
SELECT Role AS Role
FROM Table1
WHERE Table1.UserName = (SELECT Assigned_To FROM Table2);

This didn't work, but is it possible to do something like this? Should I be using a join? The columns Username and Assigned_To have the same values. I'm trying to extract the Role value from Table1, by matching it to a value in Table 2, then insert into Table2.

thank you

 

[Juice05]

Could you provide some sample data?

 

[dharkangel]

Actually, the INSERT statement needs to be an Update statement.

Here is a sample of Table1:

USERNAME ROLE
________ _____
Doe John DIR
Smith Jane EUM
Jones Tom EUM
Roney James ETM

Here is a sample of Table2:

ASSIGNED_TO ROLE
___________ ____
Rose Tim [blank, need value from query]
Smith Jane [blank, need value from query]-should be EUM
Gertner Mark [blank, need value from query]
Roney James [blank, need value from query]-should be ETM

 

[PHV]

UPDATE Table2
SET ROLE=(SELECT ROLE FROM Table1 WHERE USERNAME=ASSIGNED_TO)
WHERE EXISTS (SELECT ROLE FROM Table1 WHERE USERNAME=ASSIGNED_TO)

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

 

[dharkangel]

Thank you very much. I am using MS Access and I'm getting an 'Operation must be an updateable query' error. Are you familiar with ms access? Its installed on my PC and I'm an adminstrator so I don't think its a security problem.

 

[PHV]

MS-Access syntax:
UPDATE Table2 INNER JOIN Table1 ON ASSIGNED_TO=USERNAME
SET Table2.ROLE=Table1.ROLE

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

 

[dharkangel]

Now I'm getting an JOIN Expression Not supported, error. This is when i begin to wish I was working on SQL Server.

 

[dharkangel]

ok, here is the final outcome of the query, this one works, for anyone following this thread:

UPDATE 1_2_06 INNER JOIN UserRoleData ON [1_2_06].Assigned_To=UserRoleData.UserName SET 1_2_06.Role = UserRoleData.Role;

Thanks PHV, you helped me a lot....