Log affected rows into another table in MySQL

Question

Given the table:

  CREATE TABLE `records` (
  `id_type` varchar(50) NOT NULL,
  `old_id` INT,
  `new_id` INT,
) ENGINE=InnoDB;

And the data:

id_type | old_id | new_id
USER    | 11     | NULL
USER    | 15     | NULL
USER    | 56     | NULL
USER    | NULL   | 500
USER    | NULL   | 523
USER    | NULL   | 800

I want to perform a query that will return:

id_type | old_id | new_id
USER    | 11     | 500
USER    | 15     | 523
USER    | 56     | 800

Try to fix your table design so you won't have problems with query, or when inserting data with "new_id" then don't do insert on database. You should use then update that row (limit 1). For more help check [this](http://stackoverflow.com/questions/8356845/php-mysql-get-number-of-affected-rows-of-update-statement) and [this](http://stackoverflow.com/questions/1083866/how-to-get-number-of-rows-affected-while-executing-mysql-query-from-bash) — KuKeC, May 20 '16 at 07:29

score 1 · Answer 1 · answered May 20 '16 at 06:18

Create table records_old
(
  id_type varchar(20) primary key,
  old_id int not null
);

Create table records_new
(
  id_type varchar(20),
  new_id int not null
);


insert into records_old(id_type,old_id) values ('USER1',11);
insert into records_old(id_type,old_id) values ('USER2',12);
insert into records_old(id_type,old_id) values ('USER3',13);

insert into records_new(id_type,new_id) values ('USER1',500);
insert into records_new(id_type,new_id) values ('USER2',600);
insert into records_new(id_type,new_id) values ('USER3',700);


select * from records_old;
select * from records_new;

select a.id_type,a.old_id,b.new_id from records_old a 
inner join records_new b
where a.id_type=b.id_type;

Good, but not possible. id_type will be always something like 'USER_ID' and it will repeat over the table. — Ricardo Maia, May 20 '16 at 06:48

Arulkumar · Answer 2 · 2016-05-20T07:12:59.010

SET @old_row_number = 0;
SET @new_row_number = 0;

SELECT OldData.id_type, OldData.old_id, NewData.new_id
FROM (SELECT id_type, old_id, (@old_row_number:=@old_row_number + 1) AS OldRowNumber
      FROM `records`
      WHERE old_id IS NOT NULL) OldData
JOIN (SELECT id_type, new_id, (@new_row_number:=@new_row_number + 1) AS NewRowNumber
      FROM `records`
      WHERE new_id IS NOT NULL) NewData ON NewData.NewRowNumber = OldData.OldRowNumber

Filter with id is not null and separate as two sub-queries and add a row number for each row then join will help in your case.

Working Demo

Log affected rows into another table in MySQL

2 Answers2