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

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


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).


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);