Solved

Help with Insert SQL Query

Posted on 2011-02-15
5
265 Views
Last Modified: 2012-06-21
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.

0
Comment
Question by:TECH_NET
  • 2
  • 2
5 Comments
 

Author Comment

by:TECH_NET
ID: 34902417
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
0
 

Author Comment

by:TECH_NET
ID: 34902433
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)
0
 
LVL 6

Expert Comment

by:Gugro
ID: 34902471
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 )
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34902528


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)
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 34902548

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)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now