Link to home
Start Free TrialLog in
Avatar of TECH_NET
TECH_NET

asked on

Help with Insert SQL Query

I have 2 tables one table lists all the client name.
CLIENT_ID
CLIENT_NAME


second table refer has a column with the client name
ID,
CLIENT_NAME,
JOB_DESC
,.....

As you see table 2 has the name of the Client Name in string (varchar(255)

Now i wish to add an additional column to the second table to store the CLIENT_ID

and this client_id column needs to be updated based on the data in the table 1.

Can i do with one Single Update statement.

Avatar of TECH_NET
TECH_NET

ASKER

I know i can do it through a view

SELECT DISTINCT
      PCN.ID,ROLE_CLIENT_NAME
FROM

      JOB_REQ JR  LEFT OUTER JOIN
      PROJECT_CLIENT_NAME PCN ON JR.ROLE_CLIENT_NAME=CLIENT_NAME
I am trying like this but i get an error
UPDATE JOB_REQ
SET
      PROJECT_CLIENT_ID =
(SELECT DISTINCT PCN.ID
FROM

      JOB_REQ JR  LEFT OUTER JOIN
      PROJECT_CLIENT_NAME PCN ON JR.ROLE_CLIENT_NAME=PCN.CLIENT_NAME)
UPDATE JOB_REQ
SET
      PROJECT_CLIENT_ID =
(SELECT DISTINCT PROJECT_CLIENT_NAME.ID
FROM   PROJECT_CLIENT_NAME  WHERE  PROJECT_CLIENT_NAME.CLIENT_NAME = JOB_REQ.ROLE_CLIENT_NAME )
Avatar of Ephraim Wangoya


UPDATE JOB_REQ
SET  PROJECT_CLIENT_ID = (SELECT TOP 1 ID
                                              FROM
                                              PROJECT_CLIENT_NAME
                                              WHERE PROJECT_CLIENT_NAME.ROLE_CLIENT_NAME=JOB_REQ.CLIENT_NAME)
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial