Table Lotto contains higher draw numbers than
Table NewLotto how do you write the code to
Update NewLotto from Lotto > Lotto.Draw
??????????????????????????????????????????????

This no worky.

UPDATE newlotto n SET nDraw = l.Draw From lotto
n JOIN  l
ON n.newlotto = l.lotto
where l.Draw > n.Draw

Thanks

Rita

Rita

> Table Lotto contains higher draw numbers than
> Table NewLotto how do you write the code to
> Update NewLotto from Lotto > Lotto.Draw
> ??????????????????????????????????????????????

Presuming what you want to do is update newlotto by creating new rows which are in lotto I would use

INSERT INTO NewLotto SELECT * FROM Lotto WHERE Draw NOT IN (SELECT Draw FROM NewLotto)

This will insert all rows in Lotto which are not found in NewLotto (including earlier missing rows).

John

"John Hay"  wrote in message
news:E88B9CE9-8BE6-47E3-9B2E-69399673119D@news.elevatesoft.com...

Rita

> Table Lotto contains higher draw numbers than
> Table NewLotto how do you write the code to
> Update NewLotto from Lotto > Lotto.Draw
> ??????????????????????????????????????????????

Presuming what you want to do is update newlotto by creating new rows which
are in lotto I would use

INSERT INTO NewLotto SELECT * FROM Lotto WHERE Draw NOT IN (SELECT Draw FROM
NewLotto)

This will insert all rows in Lotto which are not found in NewLotto
(including earlier missing rows).
##############################################################################################

Thanks John it works but it flags up attached error but I can see you
can opt to stop such errors.
I dont want todo that any idea what its on about ?

Rita

use query.execsql instead of query.active

"Rita" <nospam@nospam.com> wrote in message news:371BA6C2-716F-4E2A-888F-B27A020EEBB0@news.elevatesoft.com...
>
>
> "John Hay"  wrote in message
> news:E88B9CE9-8BE6-47E3-9B2E-69399673119D@news.elevatesoft.com...
>
> Rita
>
> > Table Lotto contains higher draw numbers than
> > Table NewLotto how do you write the code to
> > Update NewLotto from Lotto > Lotto.Draw
> > ??????????????????????????????????????????????
>
> Presuming what you want to do is update newlotto by creating new rows which
> are in lotto I would use
>
> INSERT INTO NewLotto SELECT * FROM Lotto WHERE Draw NOT IN (SELECT Draw FROM
> NewLotto)
>
> This will insert all rows in Lotto which are not found in NewLotto
> (including earlier missing rows).
> ##############################################################################################
>
> Thanks John it works but it flags up attached error but I can see you
> can opt to stop such errors.
> I dont want todo that any idea what its on about ?
>
> Rita
>

"John Hay"  wrote in message
news:9C804DC5-5DC1-4205-87D6-0666E13B4BFF@news.elevatesoft.com...

use query.execsql instead of query.active
#################################################

THANKS John
works great I figured you would solve it.

Rita xxx

"John Hay"  wrote in message
news:E88B9CE9-8BE6-47E3-9B2E-69399673119D@news.elevatesoft.com...

INSERT INTO NewLotto SELECT * FROM Lotto
WHERE Draw NOT IN (SELECT Draw FROM NewLotto)
########################################################################################

Ok John not a bottle of Pimm's  insight just Tesco spring water.

How would I write that if NewLotto the one being updated
was on a local machine and Lotto was on a remote server ?

Forget the Pimm's its Scotch I need I have a sore eye tooth
just found a tiny crack at the rear near the gum.
Sorry off to the dentist. Please help.

Rita xxx

Rita
>
> How would I write that if NewLotto the one being updated
> was on a local machine and Lotto was on a remote server ?
>

You can't do a query across 2 sessions.  One option would be to use streams.

Assuming you have a local query called maxdate with "select max(date) as maxdate from newlotto", a local table called
newlottotable and a remote query called newdata with "select * from lotto where date > :date"

Something like

var
 m:tmemorystrem;

begin
 maxdate.active := true;
 newdata.parambyname('date').asdatetime := maxdate.fieldbyname('maxdate').asdatetime;
 maxdate.active := false;
 newdata.active := true;
 if not newdata.isempty then
 begin
   m := tmemorystream.create;
   try
     newdata.savetostream(m);
     newlottotable.loadfromstream(m);
   finally
     m.free;
   end;
 end;
end;


> Forget the Pimm's its Scotch I need I have a sore eye tooth
> just found a tiny crack at the rear near the gum.

Ouch

John