Creating single query with subquery, essentially a insert query with select query

Question

I've created a login function with flask, it consists of a code that opens a connection to the database then selecting a username based on the inputed username. To be more precise, here's the query:

cursor.execute("""
SELECT username FROM user_tbl 
WHERE username = %s""",
(self.username))

After that, I check the length of the fetched data, if length is 0 then I open up another connection then perform an insert query, like so:

cursor.execute("""
INSERT INTO 
user_tbl(username,password,email,user_type) 
VALUES(%s,%s,%s,%s)""", 
(self.username,self.password,self.email,self.user_type))

I've been doing this process since coding with PHP and would like to confirm if there is any way to combine these two queries. I've been researching like crazy and can't seem to find the answer... or atleast answers that work.

MySQL direct INSERT INTO with WHERE clause based on the accepted answer there, INSERT INTO...SELECT is the way to go, however after looking into it its mostly about transferring data from another table to another, I am targeting one table (my apologies if I'm missing something ).

I can't find the link, however I found another answer that mentioned that the only time you'll see a WHERE clause in an INSERT query(aside from the answer I posted above) is when you're checking if nothing 'EXISTS' (which makes sense and based on that answer I made the conclusion that having a where clause in an insert query is ok).

After checking up subquerying on a WHERE clause and following examples in this link: https://www.essentialsql.com/get-ready-to-learn-sql-server-21-using-subqueries-in-the-where-clause/ I've created my own query:

INSERT INTO user_tbl(username,password,email,user_type)
VALUES("test.test","test","test","test")
WHERE username IN 
(SELECT username FROM user_tbl WHERE username="test.test");

Reason why I chose IN is because, as mentioned in the link, once a subquery returns NULL, IN returns false for the WHERE clause (at least that's how I interpreted it).

Unfortunately, each time I run this code on my terminal I get this syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE username IN (SELECT username FROM user_tbl WHERE username="test.test")'

Hence the title, Can you guys please explain how exactly is my code syntactically wrong? Also, can you perchance pinpoint me to the right direction as I am very lost on this.

Thanks in advance,

Inno

Answer 1

you cant use the same table, but you can "hide" the SELECT then MySQL did not see this like:

INSERT INTO user_tbl(username,password,email,user_type)
VALUES("test.test","test","test","test")
WHERE username IN 
(SELECT * FROM ( 
   SELECT username FROM user_tbl WHERE username="test.test" 
   ) as myuser
);  

Answer 2

I understand that you want to INSERT a new record in user_tbl only if it does not yet exist.

MysQL has a special syntax for that, called INSERT ... ON DUPLICATE KEY UPDATE.

For this to work, you need column username to be the primary key in your table (or to have a UNIQUE constraint).

Then you can simply do:

cursor.execute(
    """
        INSERT INTO user_tbl(username,password,email,user_type) 
        VALUES(%s,%s,%s,%s)
        ON DUPLICATE KEY UPDATE username = VALUES(username)
    """, 
    (self.username,self.password,self.email,self.user_type)
)

If no record aleady exists for the given username, a new record is created. Else, the UPDATE clause is invoked (here, that would simply reassign the same value to the username , which is basically a no-op).