Mysql – finding duplicates and updating a column with values according with number of duplicates using thesql stored procedure

cursorsMySQLstored-procedures

I am using Mysql 10.1.29-MariaDB Database to create a new table.

What I am trying to do is to increment a number for each duplicate appearance of a value of company_name in another column. For example, for the table:

provided order_placed column of both table should be null

# req 


+--------------------------------------------------+
|                        req                       |
+--------------------------------------------------+
| req_id | order_placed | contact_id | seq_records |
+--------+--------------+------------+-------------+
| 1      |         null |       1000 |        null |
+--------+--------------+------------+-------------+
| 2      |         null |       1002 |        null |
+--------+--------------+------------+-------------+
| 3      |         null |       1003 |        null |
+--------+--------------+------------+-------------+



+-------------------------------------------------------+
|                               contact                 |
+-------------------------------------------------------+
| contact_id | first_name | order_placed | company_name |  
+------------+------------+--------------+--------------+
| 1000       | dirt       |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1002       | dammy      |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1003       | samii      |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1004       | xenon      |         null |       Lenova | 
+------------+------------+--------------+--------------+


CREATE TABLE `req` (
  `req_id` bigint(20) NOT NULL,
   `order_placed` char(1) COLLATE utf8_bin DEFAULT NULL,
   `contact_id` bigint(20) DEFAULT NULL,
   `seq_records` bigint(2) DEFAULT NULL,
  PRIMARY KEY (`req_id`),
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `req_ibfk_10` FOREIGN KEY (`contact_id`) REFERENCES 
  `contact` (`contact_id`)
) 
/*!40101 SET character_set_client = @saved_cs_client */;

# contact

CREATE TABLE contact (
  contact_id bigint(20) NOT NULL,
  `first_name` varchar(100) COLLATE utf8_bin NOT NULL,
  `company_name` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `company_id` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `order_placed` char(1) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`contact_id`),
  KEY `index_name` (`contact_id`),
)

query used

DELIMITER $$
DROP procedure IF EXISTS `recordsequence` $$
CREATE procedure `recordsequence` ()
BEGIN

declare companyname varchar(250);
declare recordcount integer default 0;
declare duplcount integer default 0;
DECLARE vfinished INTEGER DEFAULT 0;
declare icount int default 0;
DEClARE records_cursor CURSOR FOR
select c.company_name,count(c.company_name),r.opr_id from contact c, request r where c.contact_id=r.contact_id and r.order_placed is null  group by c.company_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET vfinished = 1;
OPEN records_cursor;
transfer_records: LOOP
FETCH records_cursor INTO companyname,duplcount;
IF vfinished = 1 THEN
LEAVE transfer_records;
END IF;

begin
set recordcount := duplcount;
set icount := 1;
DEClARE records_cursor1 CURSOR FOR
select c.contact_id,c.company_name from contact c, request r where c.company_name = companyname and c.contact_id=r.contact_id and r.order_placed is null group by c.company_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET vfinished = 1;
OPEN records_cursor1;
transfer_records1: LOOP
FETCH records_cursor INTO contactid,companyname;
IF vfinished = 1 THEN
LEAVE transfer_records1;
END IF;

begin

UPDATE contact set reorder_sequence = icount where contact_id = contactid;
set icount := icount + 1;
end;

END LOOP transfer_records1;

CLOSE records_cursor1;

if(recordcount == icount) THEN

select concat('company_name Updated successfully', companyname);

else
select concat('company_name count mismatches please check', companyname);
end if

end

END LOOP transfer_records;

CLOSE records_cursor;

End$$
DELIMITER ;

the above query is to create a procedure for the steps below

  1. To fetch records companyname and duplcount of the company names with the
    cursor.
  2. To fetch contact id of each company names and start a loop for a update
    statement.
  3. To update reorder_sequence table with values like the example given below

expected Result

Eg:  contact table

+--------------------------------------------------------+
|                         contact                        |
+--------------------------------------------------------+
| order_placed | contact_id | company_name | seq_records |
+--------------+------------+--------------+-------------+
| null         |       1002 |         Asus | 1           |
+--------------+------------+--------------+-------------+
| null         |       1003 |         Asus | 2           |
+--------------+------------+--------------+-------------+
| null         |       1005 |         Asus | 3           |
+--------------+------------+--------------+-------------+
| null         |       1006 |       Lenova | 1           |
+--------------+------------+--------------+-------------+

Like the above example i have updated seq_records column with values according to the company_name column provided both order_placed column is null

error

A syntax error occurred with code 1064 near second select statement.

Best Answer

== is not used; use just a single =.

All DECLAREs must come before the code.

When asking about 1064, always include the entire error message. The "near..." is usually an excellent clue of where the error occurred.

Related Question