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