Uli,

I've done all the work, I only suggested things that didn't work
Yeah, I'd like to hear Tim's opinion on this too.

--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:

> Discussions like this one are highly valuable - keeping these details
> in mind can save us a lot of time.  Thank you guys, mainly Uli for
> bringing this up and sharing your conclusions.

I totally agree. I was so inspired by Uli's question that I created two
tables and populated them with lots of data just to play around with in
EDB Manager. I never would have thought to do this on my own.

tblParent (200,000) rows
tblChild  (820,000) rows

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Uli


I know I'm late into this and you've found a solution but just to add the way I do it using a mix of Delphi & SQL:

1: use a query along the lines of qry1.sql.text := SELECT LIST(AnforderungenID,',') AD IDList FROM Anforderungen WHERE Datum >= date '2013-8-20'
2: Build the second query

qry2,sql.text := 'UPDATE Leistungentest SET year = year + 1 WHERE AnforderungenID IN (' +qry1.fieldbyname('IDList').AsString+')'

3: Run qry2

Its also a technique I use to create "static" filters where I want to use other tables as part of the selection process.


Roy Lambert [Team Elevate]

Roy,
> I know I'm late into this and you've found a solution but just to add the way I do it using a mix of Delphi & SQL:
>
> 1: use a query along the lines of qry1.sql.text := SELECT LIST(AnforderungenID,',') AD IDList FROM Anforderungen WHERE Datum >= date '2013-8-20'
> 2: Build the second query
>
> qry2,sql.text := 'UPDATE Leistungentest SET year = year + 1 WHERE AnforderungenID IN (' +qry1.fieldbyname('IDList').AsString+')'
>
> 3: Run qry2
>
> Its also a technique I use to create "static" filters where I want to use other tables as part of the selection process.

Good solution also! Thanks.

Uli

Roy,

<<
I know I'm late into this and you've found a solution but just to add the way I do it using a mix of Delphi & SQL:

1: use a query along the lines of qry1.sql.text := SELECT LIST(AnforderungenID,',') AD IDList FROM Anforderungen WHERE Datum >= date '2013-8-20'
2: Build the second query
qry2,sql.text := 'UPDATE Leistungentest SET year = year + 1 WHERE AnforderungenID IN (' +qry1.fieldbyname('IDList').AsString+')'

3: Run qry2
Its also a technique I use to create "static" filters where I want to use other tables as part of the selection process.
>>

Thanks for that idea. I'll trying it within EDB Manager when I get home from work (in about 9 hours).

I'd like to hear more about you create and use "static" filters but I don't think this thread is the appropriate place.
Michael Riley
GySgt USMC Retired
www.zilchworks.com

Roy,

I'm so glad IDE comes with syntax checkers cuz I suck at typing.

I'd like to hear more about  how you create and use "static" filters but I don't think this thread is the appropriate place.
Michael Riley
GySgt USMC Retired
www.zilchworks.com

Michael


I'll start a thread over in general

Roy Lambert

Roy Lambert wrote:

> I know I'm late into this and you've found a solution but just to add
> the way I do it using a mix of Delphi & SQL:
>
> 1: use a query along the lines of qry1.sql.text := SELECT
> LIST(AnforderungenID,',') AD IDList FROM Anforderungen WHERE Datum >=
> date '2013-8-20' 2: Build the second query
>
> qry2,sql.text := 'UPDATE Leistungentest SET year = year + 1 WHERE
> AnforderungenID IN (' +qry1.fieldbyname('IDList').AsString+')'
>
> 3: Run qry2

Roy,

How would you do this in EDB Manager?


--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael

>> I know I'm late into this and you've found a solution but just to add
>> the way I do it using a mix of Delphi & SQL:

>How would you do this in EDB Manager?

Since it involves a mix of Delphi & SQL I wouldn't

When I'm testing a query of this nature I'd simulate it using a typed in IN statement.

It should be possible to do the equivalent using a script. If I have time this weekend I'll see.

Roy Lambert [Team Elevate]

Michael


Had a few minutes to spare

SCRIPT
BEGIN
DECLARE IDList VARCHAR DEFAULT '';
DECLARE ResultCursor CURSOR WITH RETURN FOR ResultStmt;
EXECUTE IMMEDIATE 'SELECT LIST(CAST(_ID AS VARCHAR),'','') INTO ? FROM Companies WHERE _Name LIKE ''W%''' USING IDList;
PREPARE ResultStmt FROM 'SELECT * FROM Companies WHERE _ID IN ('+IDList+')';
OPEN ResultCursor;
END

NOTE: _ID is an autoinc hence the CAST.


Roy Lambert [Team Elevate]