Remove AKA name for Females

#1 · 2021-11-27, 9:27 am

I want to remove all "AKA" (and only AKA's) for all Females. I have over 800 rows. Do I need to account for anything else and will this statement get the job done?

Thanks

DELETE * FROM NameTable LEFT JOIN PersonTable on (PersonID = OwnerID) WHERE SEX=1 AND NameType=1

#2 · 2021-12-25, 1:37 am

I think an inner join would be better to be certain of it. Not sure if SQLite is one that forces an inner join if there is a WHERE on the outer joined table - I use too many different SQLs to remember all their quirks.
I'd also restore a backup of my database to another folder as a test database and try it on that first to make sure.

kevync has reacted to this post.

#3 · 2021-12-30, 11:30 pm

I agree with Pat on the use of the inner join but also would add that I think it is possible for an AKA to be assigned as the Primary Name and you would not want that record deleted as the person would be left with no Primary name. So this would be my suggested statement:

DELETE * FROM NameTable JOIN PersonTable on (PersonID = OwnerID) WHERE SEX=1 AND NameType=1 AND NOT IsPrimary;

kevync has reacted to this post.

#4 · 2022-01-06, 8:36 pm

thanks! I usually only pull data, not delete -- so I wanted some insight before attempting. of course I will do on a copy of my DB

#5 · 2022-01-06, 8:52 pm

Update okay -- Select works the that join, but DELETE can not be used with joins apparently. FYI ---see this https://www.py4u.net/discuss/829714

and

https://newbedev.com/how-delete-table-inner-join-with-other-table-in-sqlite

#6 · 2022-01-06, 10:36 pm

for a workaround --- I tried an update query --- since I am not using NameType 3 currently -- I figure if I got to update to "3" then I could use a simple delete from NameTable without join. But have not got that to work

Update NameTable
SET NameType = 3
FROM (SELECT Sex, PersonID from PersonTable as pt)
WHERE (pt.PersonID = OwnerID) AND pt.SEX=1 AND NameType=1 AND NOT IsPrimary;

#7 · 2022-01-23, 2:20 am

Update statements ( and delete) have to be done a particular way to use joins
/* update doesn't support joins */
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
)

#8 · 2022-01-26, 6:36 pm

Thanks Pat -- I gathered Update/Delete did not "like" Join -- but did not know how to get around that issue -- I can work with the example you provided above. Thank you for taking the time to share.

#9 · 2022-02-10, 10:20 pm

Correcting my erroneous (and untested) Dec 30 suggestion, this should work:

[code]
DELETE FROM NameTable
WHERE NameID IN
(SELECT NameID FROM NameTable
JOIN PersonTable on (PersonID = OwnerID)
WHERE SEX=1 AND NameType=1 AND NOT IsPrimary
);
[/code]

kevync has reacted to this post.

#10 · 2022-02-11, 1:05 pm

THANKS TOM ---Ah -- that looks like it should work and simple. I know the joins are different with SQLite compare to some SQL .