SOQL to update a field of an OpportunityLineItem in the opportunity in use

Question

Trying to update an OpportunityLineItem, but need to be able to look for that particular product in the opportunity.

Problem:

SOQL is using the List Price from the product but from a different opportunity

Error:

in (SELECT OpportunityId FROM OpportunityLineItem)LIMIT 1 ^ ERROR at Row:4:Column:103 The inner and outer selects should not be on the same object type

I understand what the error is, but can't figure out a solution to get what I need. Any ideas?

SOQL

OpportunityLineItem optLIProductB = [SELECT OpportunityId, Opportunity.Pricebook2Id, Name, ProductCode , PricebookEntryId, Quantity, UnitPrice
                                                        FROM OpportunityLineItem
                                                        WHERE ProductCode = '1b0000' 
                                                        AND OpportunityId in (SELECT OpportunityId FROM OpportunityLineItem)LIMIT 1];

Answer 1

You need to query by a particular id, or a list of ids, instead of all available ids. You can use a bind to provide an apex variable to a query for use in a filter.

From the Using Apex Variables in SOQL:

Account A = new Account(Name='xxx');
insert A;
Account B;

// A simple bind
B = [SELECT Id FROM Account WHERE Id = :A.Id];

You can also use a Id or a List<Id>.

List<Id> ids = new List<Id>(); // set from somewhere, or get from a list of sObjects
Id someId = 'a0Cc000000PAWBv'; // For example only, avoid hardcoding ids

List<Account> accounts = [SELECT ID, Name FROM Account WHERE Status = 'Active' AND Id IN :Ids];

Account account = [SELECT ID, Name FROM Account WHERE Status = 'Active' AND Id = :someId LIMIT 1];

---

This is a general example on how to use these variables in queries. Try to modify your code to use these, and either edit your question, or ask a new question if you encounter issues with your code.

Answer 2

Id opportunityId;
// assign opportunityId with proper Opportunity you are working with
List<OpportunityLineItem> optLIProductB = [SELECT OpportunityId, Opportunity.Pricebook2Id, Name, ProductCode, PricebookEntryId, Quantity, UnitPrice
                                                        FROM OpportunityLineItem
                                                        WHERE ProductCode = '1b0000' 
                                                        AND OpportunityId :opportunityId
];

Please, don't query to a single object. Query in List. Then, you can check the size of the list. In case of single object you may have System.QueryException.