Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Update with Join and Where clause |
Sun, Jul 17 2011 6:56 AM | Permanent Link |
Rita Tipton | 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 |
Mon, Jul 18 2011 9:47 AM | Permanent Link |
John Hay | 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 |
Mon, Jul 18 2011 10:48 AM | Permanent Link |
Rita Tipton | "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 Attachments: error.JPG |
Mon, Jul 18 2011 1:13 PM | Permanent Link |
John Hay | 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 > |
Mon, Jul 18 2011 4:37 PM | Permanent Link |
Rita Tipton | "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 |
Fri, Jul 22 2011 4:29 AM | Permanent Link |
Rita Tipton | "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 |
Fri, Jul 22 2011 6:52 AM | Permanent Link |
John Hay | 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 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |