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.

TECH_NETAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TECH_NETAuthor Commented:
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
TECH_NETAuthor Commented:
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)
GugroCommented:
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 )
Ephraim WangoyaCommented:


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)
Ephraim WangoyaCommented:

I'm not sure if I switched the fields around so you can try this as well

PDATE JOB_REQ
SET  PROJECT_CLIENT_ID = (SELECT TOP 1 ID
                                              FROM
                                              PROJECT_CLIENT_NAME
                                              WHERE PROJECT_CLIENT_NAME.CLIENT_NAME =JOB_REQ.ROLE_CLIENT_NAME)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.