• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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)
0
programmher
Asked:
programmher
  • 7
  • 3
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now