i have an sql insert query that take values from user input and also insert the ID from another table as foreign key. for this is write the below query but it seems not working.

Status_type table

CREATE TABLE status_type (
ID int(5) NOT NULL,
status varchar(50) NOT NULL
);

info table

CREATE TABLE info (
ID int(11) NOT NULL,
name varchar(50), NULL
nickname varchar(50), NULL
mother_name varchar(50), NULL
birthdate  date, NULL
status_type int <==this must be the foreign key for the status_type table
create_date date
);

for the user he has a dropdownlist that retrieve the value from the status_type table in order to select the value that he want to insert into the new record in the info table where as the info table take int Type because i want to store the ID of the status_type and not the value

code:

    query = '''
            INSERT INTO info (ID,name,nickname,mother_name,birthdate,t1.status_type,created_date)
            VALUES(?,?,?,?,?,?,?)
                      
            select t2.ID
            from info as t1
            INNER JOIN status_type as t2
            ON t2.ID = t1.status_type
            '''    
            args = (ID,name,nickname,mother_name,db,status_type,current_date)
                       
   cursor = con.cursor()
   cursor.execute(query,args)
   con.commit()
   st.success('Record added Successfully')

the status_type field take an INT type (the ID of the value from another table ). So when the user insert it insert the value.

What i need is to convert this value into its corresponding ID and store the ID

CodePudding user response：

based on the answer of @Mostafa NZ I modified my query and it becomes like below :

query = '''
            INSERT INTO info (ID,name,nickname,mother_name,birthdate,status_type,created_date)
            VALUES(?,?,?,?,?,(select status_type.ID 
                        from status_type
                        where status = ?),?)
                      
            '''    
            args = (ID,name,nickname,mother_name,db,status_type,current_date)
                       
   cursor = con.cursor()
   cursor.execute(query,args)
   con.commit()
   st.success('Record added Successfully')

CodePudding user response：

When creating a record, you can do one of these ways.

Receive as input from the user
Specify a default value for the field

INSERT INTO (...) VALUES (? ,? ,1 ,? ,?)
Use a select in the INSERT

INSERT INTO (...) VALUES (? ,? ,(SELECT TOP 1 ID FROM status_type ODER BY ID) ,? ,?)

When INSERT data, you can only enter the names of the destination table fields. t1.status_type is wrong in the following line

INSERT INTO info (ID,name,nickname,mother_name,birthdate,t1.status_type,created_date)