0

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.

Improve this question
1
  • 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. Commented Jun 30, 2019 at 5:40

2 Answers 2

Reset to default
0

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

Improve this answer
2
  • near Delcare records_cusor1 Cursor FOR Commented Jun 30, 2019 at 10:02
  • 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. Commented Jun 30, 2019 at 14:50
0

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

Improve this answer
1
  • i got a error near row_number() over ( Commented Jun 30, 2019 at 10:27

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.