finding duplicates and updating a column with values according with number of duplicates using mysql stored procedure

Question

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.

• Lennart - Slava Ukraini: When you say reorder_sequence, do you mean seq_records? Instead of ASCII art tables. use INSERT statements. I can only speak for myself, but having to create insert statements myself is a showstopper for trying to answer a question.

Answer 1

== 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.

• Ameenur Rahman: near Delcare records_cusor1 Cursor FOR
• Rick James: SET statements are sitting before DECLARE, so my second statement applies. The near pointed to the right spot but did not say why it is a syntax error.

Answer 2

analytical / window functions :) pls do not reinvent a wheel :)

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
) 
;
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
) ;
INSERT INTO req VALUES (1, NULL, 1000, NULL);
INSERT INTO req VALUES (2, NULL, 1002, NULL);
INSERT INTO req VALUES (3, NULL, 1003, NULL);
INSERT INTO contact VALUES (1000, 'dirt','Asus', 12, NULL);
INSERT INTO contact VALUES (1002, 'dammy', 'Asus', 12, NULL);
INSERT INTO contact VALUES (1003, 'samii', 'Asus', 12, NULL);
INSERT INTO contact VALUES (1004, 'xenon', 'Lenova', 1, NULL);

select:

SELECT order_placed
, contact_id
, company_name
, row_number() over (partition by company_name order by contact_id) as seq_records
FROM contact;

result:

| order_placed | contact_id | company_name | seq_records |
| ------------ | ---------- | ------------ | ----------- |
| | 1000 | Asus | 1 |
| | 1002 | Asus | 2 |
| | 1003 | Asus | 3 |
| | 1004 | Lenova | 1 |

---

View on DB Fiddle (make sure you select MySQL 8 .. v5 does not support window functions

• Ameenur Rahman: i got a error near row_number() over (