Delete names Female Alt names

#1 · 2021-03-05, 8:43 am

I want to delete rows where a Female has an ALT name (Type 1). The below does not work -- what do I need to update SQL so it work. (My Sql experience is mostly limited to Retrieving info -- not updating or deleting)

Delete nt
FROM NameTable as nt
LEFT JOIN PersonTable as pt ON (OwnerID = PersonID)
WHERE NOT IsPrimary AND NameType = 1 and Sex = 1

#2 · 2021-03-05, 7:02 pm

I figured out a workaround to accomplish both of my goals, plus almost a third. I ended up exporting a GEDCOM. Importing to RM7, performing the Sql updates, exporting to GEDCOM then importing to a new RM8 db. So now all my ALT names are correct. Also all media is free from errors (because I have no media in the db). Since I have over 7000 media items, large majority from Ancestry, I need a way to exam the Media & Medialimks table as well as any "related" tables so I can correct them without manually reading them (RM is way to slow for this). A few hundred would be manageable -- not 7000+. Another challenge would be since they cam from Ancestry they do not have a user friendly name. I can identify them from old tables -- not sure if want to go about renaming everything. ("1900 US Census (231)".... to "1900 US Census John_and_Betty_Smith Mytown MyState")

#3 · 2021-03-07, 1:22 am

@kevync The reason the delete didn't work is probably a quirk of SQLite syntax in that it doesn't support plain ordinary UPDATE... FROM and DELETE ... FROM like other SQL databases.
There are workarounds if you Google - you have to do a subquery that returns the values you require and a where clause for what to change.

I refer back to this skeleton code for updates

/* update doesn't support FROM */
UPDATE table_a
SET
(column_a_1, column_a_2) = (SELECT table_b.column_b_1, table_b.column_b_2
FROM table_b
WHERE table_b.user_name = table_a.user_name )
WHERE
EXISTS (
SELECT *
FROM table_b
WHERE table_b.user_name = table_a.user_name
)

kevync has reacted to this post.

#4 · 2021-03-08, 9:44 pm

Thanks Pat. Still learning about differences between SQL's

#5 · 2021-03-21, 3:02 am

Kevync - that one pains me too since I automatically write SQL Server T-SQL syntax as used at work for the past 25 years. SQLite is pretty close to it mostly.
Then at work I also have to grab data from MySQL or even worse, Oracle databases and that's many more differences!