Updatng a temp table

I have a temp table I am trying to update with values from another column from another table that I have to join on itself.  I keep getting a subquery error.  How do I resolve this subquery error?  The values will be different on some occasions so I may have more than one value returned on my select:
update #mytemptable
                  set price_ID =  (Select tbl1.price_ID  FROM loaded_pricecodes as lc
              LEFT JOIN
              loaded_pricecodes tbl2
              ON lc.parent_price_code=tbl2.INTERNAl_Price_Code
             WHERE lc.INTERNAL_Price_Code = '777777'
             AND lc.price_ID IS not NULL)
programmherAsked:
Who is Participating?
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.

BartVxCommented:

You are selecting tbl1.price_ID, but the alias for the first table is not tbl1, but lc.

Is it your objective to update with the price of tbl2, if tbl1 has a parent?

If so, does this do the trick?

update #mytemptable
                  set price_ID =  (Select TOP 1 ISNULL(tbl2.price_ID, lc.price_ID)  FROM loaded_pricecodes as lc
              LEFT JOIN
              loaded_pricecodes tbl2
              ON lc.parent_price_code=tbl2.INTERNAl_Price_Code
             WHERE lc.INTERNAL_Price_Code = '777777'
             AND lc.price_ID IS not NULL)
0
programmherAuthor Commented:
Unfortunately, that did not work.  The top record as null so all fieods ere updated with null instead of the actual corresponding code.
0
programmherAuthor Commented:
Sorry - I re-ran it and it updated my column with the same price code for all the records.  There should be at least two different price codes for my recordser.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BartVxCommented:
Your subquery has no relation to your temp table, so the subquery will indeed always return the same result for each record of your tem table.

Can you explain exactly how the temp table should be updated?
0
jvejskrabCommented:

Try to describe update logic by words
I think some join to the #mytemptable is ommited in the update statement
Maybe something like this....

update m
   set price_ID =  a.price_ID
   from  #mytemptable m
     JOIN (Select tbl1.price_ID, some_ID_column
               FROM loaded_pricecodes as lc
                          LEFT JOIN loaded_pricecodes tbl2
                                ON lc.parent_price_code=tbl2.INTERNAl_Price_Code
               WHERE lc.INTERNAL_Price_Code = '777777'
                           AND lc.price_ID IS not NULL) a
     ON a.some_ID_column = m.some_ID_column
0
programmherAuthor Commented:
Bartvx,

The temp table includes fields from 3 tables.

I created a new field in the temp table that needs to be updated to display the price code from te loaded_price_code table where the price_if is 777777.  This new fields might contain new and old price codes.  This is why I am joining the table on itself.

jvejskrab - I will try your suggestion and let you know if it works.
0
programmherAuthor Commented:
jvejskrab -

My field updated with all nulls.

Could I accomplish this with a cursor?  If so, do you have an example of a cursor I could use?  I can output the records I need with a simple select statement - it's the updating that is the last obstacle...
0
BartVxCommented:
Programmher,

is one of the 3 fields in the temp table related to a field in the loaded_pricecodes table? (for example is INTERNAl_Price_Code a field in the temptable?

We really need to establish some kind of relation between the rows in the temp table and the rows in the loaded_pricecodes table to do the update.
0
jvejskrabCommented:

programmher-

Could I accomplish this with a cursor?  If so, do you have an example of a cursor I could use?  I can output the records I need with a simple select statement - it's the updating that is the last obstacle...


Could you send us the select statement which works?
I dont recommend using cursor......its possible to do this, but you should avoid that whenever its possible
0
programmherAuthor Commented:
I wrote a cursor.  It outputs the correct priceid list but still updates just one priceid into my new temp table feld:

 DECLARE @priceid varchar(50)
DECLARE @getpriceid CURSOR
   SET @getpriceid = CURSOR FOR Select tmpric.prive_ID  FROM UPLOADed_prices as LC
              LEFT JOIN
              UPLOADed_prices tmpric
              ON Lc.PARENT_PRICE_ID=TMPRIC.INTERNAL_PRICE_ID
             WHERE Lc.PRICE_NUM = '777777'
             AND Lc.PRICE_ID IS not NULL              
             
         
             
OPEN @getPRICEid
FETCH NEXT
FROM @getPRICEID INTO @PRICEid
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @PRICEid  
 FETCH NEXT
FROM @getPRICEid INTO @PRICEid
UPDATE #tmpPRICETABLE set NEW_MASTER_ID  = @PRICEID
where #tmpPRICETABLE.NEW_PRICE_NUM = 777777
AND e #tmpPRICETABLE.NEW_PRICE_ID IS not NULL  
END
CLOSE @getPRICEid
DEALLOCATE @getPRICEid
This is the last step to this process...any suggestions?
0
programmherAuthor Commented:
Added an additional criteria to update statement in the cursor.  Issue resolved.  Thanks for everyone's help and insight!
0

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
programmherAuthor Commented:
I added an addition criteria to my update statement and my cursor worked.  This was related to a separate answer that I already accepted as a solution.
0
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.