Updating one table with join to another table

Hi,

I have a problem with an update- "statement". I just don't know how to handle this with lightspeed. Maybe someone can help me here.

First of all my two tables:

Table [Document] with columns: [Id] | [State] | ...

1:n

Table [DocumentData] with columns: [Id] | [DocumentId] | [State]

I have to update column [State] in table [Document] but only if in table [DocumentData] is a dependent entry which column [Type] is 1.

Maybe the best thing I can do is just posting the SQL-Statement I want lightspeed to execute in background ;)

The expected statement is:

update document set State=20 where Id in (select Id from Document x inner join DocumentData y on x.Id = y.DocumentId where x.State = 10 and y.Type = 1)

could someone help me with this problem?

thanks a lot

If you want to perform a bulk update like this you could look at using IUnitOfWork.Update(Query query, object attributes). Failing that you will need to look at using a stored procedure or manually execute a SQL statement via ADO.NET which you can attach to the connection LightSpeed is using by using IUnitOfWork.PrepareCommand().

Jeremy

thanks for your answer! I'm using the unitOfWork.Update-Methode many times but in this case i just don't now how to create the correct query. Maybe the only way is to write the sql-statement by my own or is it possible to use something similar to sql-IN-Statement in a query?

If you can send through a small repro project with your model and the query you are trying to produce then I can try and have a look at this for you but otherwise it sounds like you may need to use the IUnitOfWork.PrepareCommand() approach as that will allow you to specify arbitrary SQL.

Jeremy