Insert into a table from multiple table(MySql)-CodePudding

insert ignore into user_login_history 
        (`created_at`,`updated_at`,`created_by`,
         `updated_by`,`USER_ID`,`TENANT`,
         `LAST_LOGIN`,`deleted`,`published`)
values(
    (SELECT 
        a.created_at,
        a.updated_at,
        a.created_by,
        a.updated_by,
        user.id,
        a.tenant,
        a.created_at,
        b'0',
        b'1'
    FROM
    (SELECT audit_log_summary.*
     FROM audit_log_summary, (SELECT 
        user, MAX(created_at) AS created_at, tenant
    FROM
        audit_log_summary
    WHERE
        audit_log_summary.REVISION_TYPE = 'LOGGED_IN'
    GROUP BY user , TENANT) max_user
    WHERE
        audit_log_summary.user = max_user.user
            AND audit_log_summary.tenant = max_user.tenant
            AND audit_log_summary.created_at = max_user.created_at) a
        INNER JOIN
    user ON a.user = user.email));

error i am getting :

Error Code: 1136. Column count doesn't match value count at row 1

basically I have 3 tables i want to populate data in one table fetching the data from other two..

Table to be populated: user_login_history Tables From which datas are fetched: audit_log_summary & user

CodePudding user response：

You have to remove VALUES from the clause, so instead of using INSERT INTO VALUES, use INSERT INTO SELECT FROM. Maybe SELECT audit_log_summary.* you should select the needed columns only. In your select you are selecting a.created_at twice, check if it is what you want. Maybe this will help:

insert ignore into user_login_history  (`created_at`,
                                        `updated_at`,
                                        `created_by`, 
                                        `updated_by`,
                                        `USER_ID`,
                                        `TENANT`,
                                        `LAST_LOGIN`,
                                        `deleted`,
                                        `published`)
(SELECT 
        a.created_at,
        a.updated_at,
        a.created_by,
        a.updated_by,
        user.id,
        a.tenant,
        a.created_at,
        b'0',
        b'1'
 FROM
    (SELECT audit_log_summary.*
     FROM audit_log_summary, (SELECT 
        user, MAX(created_at) AS created_at, tenant
    FROM
        audit_log_summary
    WHERE
        audit_log_summary.REVISION_TYPE = 'LOGGED_IN'
    GROUP BY user , TENANT) max_user
    WHERE
        audit_log_summary.user = max_user.user
            AND audit_log_summary.tenant = max_user.tenant
            AND audit_log_summary.created_at = max_user.created_at) a
        INNER JOIN
    user ON a.user = user.email));