Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Dec 18, 2018 4:00 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: Syntax for deleting from 2 SQL tables
PostPosted: Tue Oct 25, 2005 12:47 pm 
Offline

Joined: Fri Sep 30, 2005 2:22 pm
Posts: 47
Location: San Rafael, CA
I would like to delete records containing the same name from 2 sql tables. The syntax I am using to get the records is this:

rs=App.FeedDB.SQLSelect("Select Properties.*, Spaces.* from Properties, Spaces where PropNum='"+propertyIndex+"'")

Both the Properties and the Spaces tables have the column PropNum in them.

This code doesn't work, but as far as I can tell, the way to include several tables in a Select statement is to separate them by commas. This must not be correct.

Thanks in advance. Mike....


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 12:58 pm 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
Are you trying to delete or select the records? For selecting the records you want to use a join in which you list the tables seperated by columns and then you define the relationship in the where clause.

In your case since both tables will share the PropNum you can select all the records like this:

select * from Properties, Spaces where Properties.Propnum = Spaces.Propnum AND Spaces.Propnum = x


There we list the tables in the comma seperated list and the first part of the where clause we define the relationship between the two tables: 'Properties.Propnum = Spaces.Propnum'.

After the relationship is defined we finish the query to show just the records we are looking for. This will select the records from both tables that are related.

If you want to delete the records from the table then you'll want to run seperate delete statements for each table. Its best to do this in a transaction so that if an error occurs you can rollback and all the data will remain untouched and you will not be left in a state where only half the records were deleted.

Hope this helps.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 1:17 pm 
Offline

Joined: Fri Sep 30, 2005 2:22 pm
Posts: 47
Location: San Rafael, CA
Mike,

Isn't there a simpler way to select a record set (rs) and then follow that by a rs.deleterecord instruction (the line I left out on the original post)?

How about something like this:

rs=App.FeedDB.SQLSelect("Select * from Properties, Spaces where Properties.PropNum='"+propertyIndex+"' OR Spaces.PropNum='"+propertyIndex+"'")

I really don't have to define the relationship between the two tables in the sense that the PropNum that I'm after already exists in both tables. I just want to retrieve all records in the two tables that have exactly the same PropNum and then delete the record set in one command. I check for nil conditions after the record set selection and prior to rs.deleterecord in my program.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 1:39 pm 
Offline

Joined: Fri Sep 30, 2005 2:22 pm
Posts: 47
Location: San Rafael, CA
Mike,

I followed your syntax and didn't get a nil exception when the rs got created, but the rs.deleterecord followed by a commit statement didn't actually delete the records I wanted removed.

The record set syntax I used was:

rs=App.FeedDB.SQLSelect("Select * from Properties, Spaces where Properties.PropNum=Spaces.PropNum AND Properties.PropNum='"+propertyIndex+"'")


You suggested deleting records from each table separately, but if I use that strategy there doesn't seem to be any need for the Select * Properties, Spaces..... joint statement. I know that I can delete records separately by just performing the rs and rs.deleterecords sequence twice, once for each table. I was just looking for a shorter, more clean way. Perhaps I should just do it brute force.

Thanks, Mike.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 1:54 pm 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
I don't believe that you can delete from a recordset that is the result of a join. In order to call delete you'll have to call RecordSet.Edit and when calling either of these functions if you check the Database.ErrorMessage property I think you will see an error that you cannot edit the recordset.

Also about your selecting the records in the join you will want to define the relationship between the tables. If there is no relationship between the tables then database engines will return the records that match along with all the records from the other table.

For example if you have two tables (a and b) that have 3 records each:

a1,a2,a3 and b1,b2,b3

If you run this query without a relationship:
select a.name, b.name from a,b where b._rowid = 2 OR a._rowid = 2

You get the following results back
a2,b1 + a2,b2 + a2,b3 + b2,a1 + b2,a3


If you define the relationship like this:
select a.name, b.name from a,b where a._rowid = b._rowid AND a._rowid = 2

Then you only get back the records that match since the engine knows how these tables relate to each other:
a2,b2


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 1:59 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
why are you even making a recordset ? If you want clean code, just delete the records from the table using

SQLExecute("Delete From Table_Name where [expression] ")

making a recordset doesn't make much sense to me, however I guess there could be some places where you need it.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 2:06 pm 
Offline

Joined: Fri Sep 30, 2005 2:22 pm
Posts: 47
Location: San Rafael, CA
I'm using record set because I've been learning from the REALbasic documentation and it seems that this is the way to manipulate records in REALSQLdatabases.

Am I wrong? Have I been working too hard? I clearly am new to SQL and have been learning by examples in the documentation that are can't show all the various ways of skinning a cat.

Thanks.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 2:13 pm 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
You can use the recordset to edit and delete records but this will only work when the recordset is displaying records from a single table. If you have queried records from multiple tables then you cannot modify the resulting recordset.

Like I mentioned in my first post I think you will find the best way to delete from two tables is to run a delete statement for each table.
db.sqlExecute "delete from a where _rowid = 2"
db.sqlExecute "delete from b where _rowid = 2"


Hope this helps.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 2:23 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
It's hard to explain the documentation that comes with REALBasic at the best of times, all I can figure is that their old databases (which they wrote themselves) didn't use standard SQL all the time.

SQLExecute allows you talk directly to the SQLite database engine and because of that you can use SQLite syntax. Anything you find at http://www.sqlite.org in the syntax area can be used by talking to the engine as it's the engine doing the work, not Realbasic, therefore it doesn't matter if Realbasic understands it.

A good example is changing field values. Do you Edit a recordset or use the SQL Update statement. You have to deside which is going to work best for you and use it, or a combo of the two.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 25, 2005 2:56 pm 
Offline

Joined: Fri Sep 30, 2005 2:22 pm
Posts: 47
Location: San Rafael, CA
Many thanks to Mike and Jym.

I really appreciate the time you have taken to educate me. I'll start using the straightforward SQL code now. The deletions, by the way, work fine now that I just execute on each table separately. Piece of cake.

Mike


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group