MySQL syntax error when creating a table with data from two other tables

Question

I am creating a new table with data from another table. I would like to combine the values from an Income_Ledger table as well as an Expenditure_Ledger table, with a WHERE condition, into a new table.

I can successfully add the values if only using one of the tables, but I can’t seem to write the SQL syntax to perform the input from both tables. i.e. the AS SELECT, FROM, WHERE works if only using one of the two tables.

Thanks for any help

MySQL (ver 14.14 Distr 5.7.23)

# bank in for below gives strings of: B1 and B2
for bank in Bank_Account_Code:
    with engine.connect() as con:
        con.execute('''
        DROP TABLE IF EXISTS '''+bank+''';''')
        con.execute('''
        CREATE TABLE IF NOT EXISTS '''+bank+'''
        AS SELECT id,Date,CR
        FROM Income_Ledger
        WHERE DR_code="'''+bank+'''"

        AS SELECT id,Date,DR
        FROM Expenditure_Ledger
        WHERE CR_code="'''+bank+'''"

        ORDER BY Date
        ;''')

Creating a separate table for each entity is a really bad idea under most circumstances. Please explain what problem you are trying to do. — Gordon Linoff
– Gordon Linoff, Commented May 5, 2020 at 10:47
Create a view instead. No copying of data needed, will always be up-to-date. — jarlh
– jarlh, Commented May 5, 2020 at 10:48

barianos · Accepted Answer · 2020-05-05 10:53:38Z

1

You cant simply select two tables and put them into one. Depending on how you want to combine the data you can either JOIN the tables, or use UNION. Seeing the CR_code in the two tables, UNION is your best bet for this situation.

https://www.w3schools.com/sql/sql_union.asp https://www.w3schools.com/sql/sql_join.asp

answered May 5, 2020 at 10:53

barianos

525 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tedioustortoise Over a year ago

for bank in Bank_Account_Code:     with engine.connect() as con:         con.execute('''         DROP TABLE IF EXISTS `'''+bank+'''`         ;''')         con.execute('''         CREATE TABLE IF NOT EXISTS '''+bank+'''         SELECT id, Date, DR_code, DR, CR, CR_code FROM Income_Ledger         WHERE DR_code="'''+bank+'''"         UNION ALL         SELECT id, Date, DR_code, DR, CR, CR_code FROM Expenditure_Ledger         WHERE CR_code="'''+bank+'''"         ORDER BY DATE         ;''')

Collectives™ on Stack Overflow

MySQL syntax error when creating a table with data from two other tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related