Difficult Query 2

[MichaelF81]

I am working on an update statement, and here is some background.

I am writing a DTS package and the first step is to import a txt file into a table called 'A_Imp' (well the first step is to make sure that 'A_Imp' is empty.).

Below is the table structure.

CREATE TABLE A_Imp (
  full_name varchar (150) ,
  em_id varchar (150) ,
  dp_name varchar (150) ,
  dp_id varchar (150) ,
  job_title varchar (150) ,
  u_std_desc varchar (150) ,
  em_std varchar (150) ,
  u_hiredate varchar (150) ,
  lname varchar (150) ,
  fname varchar (150) ,
  dv_Id varchar (150)
)

Now most of those fields are in the text file, but 'lname', 'fname' and 'dv_id' are not. My issue is the 'dv_id' field.

I need to pull the dv_id from the dv table, but there is no way to link the 'A_Imp' and 'dv' tables. There is a way to link the 'dp' and 'dv' table, and the 'dp' table can be linked to 'A_Imp' via 'dp_id'.

Select afm.dv.dv_id, afm.dp.dp_id, afm.dv.[name]
from afm.dp inner join afm.dv on afm.dv[name] = afm.dp.[name]

That is the code to link the two tables, but now I need to update the 'A_Imp.dv_id' field using that.

Any thoughts? Below is what I have gotten stuck at.

update A_Imp
set A_Imp.dv_id = (select afm.dv.dv_id from afm.dv inner join afm.dp on afm.dp.[name] = afm.dv.[name])
where A_Imp.dp_id = afm.dp.dp_id
GO

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[AlexCuse]

Is there any field in this query

Select afm.dv.dv_id, afm.dp.dp_id, afm.dv.[name]
from afm.dp inner join afm.dv on afm.dv[name] = afm.dp.[name]

That you can join A_imp to?

Ignorance of certain subjects is a great part of wisdom

 

[MichaelF81]

Oh, by the way, the error I get for the above query

update A_Imp
set A_Imp.dv_id = (select afm.dv.dv_id from afm.dv inner join afm.dp on afm.dp.[name] = afm.dv.[name])
where A_Imp.dp_id = afm.dp.dp_id
GO

is

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'afm.dp' does not match with a table name or alias name used in the query.

Thanks




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

Try this...

Update A_Imp
Set    A_Imp.dv_id = dv.dv_id
From   A_Imp
       Inner Join dp
          On A_Imp.dp_id = dp.dp_id
       Inner Join dv
          On db.[name] = dv.[name]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Ah... that last line should be...

On d[!]p[/!].[name] = dv.[name]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

That is because you need to use your subquery as a derived table, and specify FROM in your update. But because (I presume) your subquery is going to return > 1 result, you will not be able to update even if syntax is fixed.

Is there a one-to-one relationship to be found between any column found in any table used by your subquery and your A_imp table?

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Is there a one-to-one relationship to be found between any column found in any table used by your subquery and your A_imp table?

I am going to go over to the 'learn to read' forum and see if I can find any help over there

Ignorance of certain subjects is a great part of wisdom

 

[MichaelF81]

@ George - thanks again for the help!

@ Alex - the sub-query will only return 1 value so it works just fine.




"Adults are just obsolete children and the hell with them." - Dr. Seuss