Solved

Help with Insert SQL Query

Posted on 2011-02-15
5
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Ephraim Wangoya
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:
Ephraim Wangoya 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

635 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