CODEWITHSUNDEEP
sqlsqlite
user3000921
user3000921

Reputation: 43

Inserting and deleting over multiple tables

So there's the reference table (let's call it "Users"), which has two columns that interest me: UserName (string) and isVIP (boolean). There usernames are present in three other tables: UserDocs (in the User column), UserClearance and UserCert (in UserName column).

From those three tables, I need to delete names that are present in Users.UserName and have isVIP of 0. Now I'm doing it by running three separate nested queries:

DELETE FROM UserDocs WHERE User in (SELECT UserName FROM Users WHERE isVIP = 0);
DELETE FROM UserClearance WHERE UserName in (SELECT UserName FROM Users WHERE isVIP = 0);
DELETE FROM UserCert WHERE UserName in (SELECT UserName FROM Users WHERE isVIP = 0);

Is there a way I can do something like DELETE FROM UserDocs, UserClearance, UserCert WHERE User OR UserName in (SELECT UserName FROM Users WHERE isVIP = 0);? I know this exact string is wrong, but you get the idea.

That's question number one.

Question number two is about inserting in a similar (but not exact) fashion: I need to populate the UserCert table, assigning several different DocTypes to every non-VIP user in the reference table. What I'm doing right now:

INSERT OR IGNORE INTO UserCert (UserName,DocType) SELECT UserName,'Card_FRM' FROM Users WHERE isVIP=0;
INSERT OR IGNORE INTO UserCert (UserName,DocType) SELECT UserName,'Card_GST' FROM Users WHERE isVIP=0;
INSERT OR IGNORE INTO UserCert (UserName,DocType) SELECT UserName,'Card_INV' FROM Users WHERE isVIP=0;
INSERT OR IGNORE INTO UserCert (UserName,DocType) SELECT UserName,'Card_REG' FROM Users WHERE isVIP=0;
INSERT OR IGNORE INTO UserCert (UserName,DocType) SELECT UserName,'Passport' FROM Users WHERE isVIP=0;

What I need to do is INSERT OR IGNORE INTO UserCert (UserName,DocType) SELECT UserName,('Card_FRM','Card_GST','Card_INV','Card_REG','Passport') FROM Users WHERE isVIP=0;, which, of course, is also wrong, but again, you get the idea: there should be five rows per username, each with its own different DocType.

How do I go about it?

Upvotes: 0

Views: 81

Answers (2)

forpas
forpas

Reputation: 164099

You can't delete from more than 1 tables in a single statement.

You can insert in a single statement rows with the use of VALUES:

INSERT OR IGNORE INTO UserCert (UserName, DocType) 
SELECT u.UserName, v.* 
FROM Users u 
CROSS JOIN (VALUES ('Card_FRM'), ('Card_GST'), ('Card_INV'), ('Card_REG'), ('Passport')) v
WHERE isVIP=0;

This code will (try to) insert 5 rows in the table UserCert for each row returned from the table Users.

Upvotes: 1

Erwin Smout
Erwin Smout

Reputation: 18408

Q1 : No.

Q2 : You can write a select for a 5-row table for your 5 doctype literals, JOIN that to (SELECT UserName FROM ... WHERE isVIP = 0) and you can then INSERT the result of that JOIN into your target table using one single INSERT. There is more than 1 way to write SQL for "table literals" but a sequence of UNIONs of 1-row tables is the most straightforward I believe.

(PS that's the correct answer for standard SQL, which is the SQL version you'd be targeting with that 'SQL' tag (hover over it and read what it says). Specific dialects may have different answers (though I'm not aware of any that would make the answer to Q1 'yes') but you should identify which one you're thinking of by using the appropriate dbms tag.)

Upvotes: 2

Related Questions