Stored Procedure IF statement in MY SQL returing error 1241

Question

I am writing this code in MYSQL workbench to check for the data in several tables in order to enter a customer and various customer details in multiple tables without duplication and to gather the primary key for the variable from each table before moving onto the next one. I used the same IF ELSE syntax for an INSERT Statement that worked flawlessly but it is failing with the addition of the SELECT statement in the beginning to check for the customerID in a view and the UPDATE statement at the end instead of INSERTS only.

It's failing with error 1241 Operand should contain 1 column(s)

I tried to find the answer to this and found lots of questions about this error but none pertaining to this syntax.

Can anyone help? Code is below

CREATE DEFINER=`U03qew`@`%` PROCEDURE `sp_modify_customer`(IN CUST_ID INT(11), IN CUST_COUNTRY VARCHAR(50), 
IN CUST_NAME VARCHAR(45), IN CUST_ADDRESS VARCHAR(50), IN CUST_ADDRESS2 VARCHAR(50), IN CUST_CITY VARCHAR(50), 
IN CUST_ZIP VARCHAR(10), IN CUST_PHONE VARCHAR(20), IN CUR_USER VARCHAR(50))
BEGIN
DECLARE ccountryId VARCHAR(50);
DECLARE ccityId VARCHAR(50);
DECLARE caddressId VARCHAR(50);
IF (SELECT * FROM customer WHERE customerId = CUST_ID) IS NOT NULL THEN
BEGIN
IF (SELECT countryId FROM country WHERE country = CUST_COUNTRY) IS NOT NULL THEN
BEGIN
SELECT countryId FROM country WHERE country = CUST_COUNTRY
INTO ccountryId;
END;
ELSE BEGIN
INSERT INTO country (country, createDate, createdBy, lastUpdate, lastUpdateBy)
VALUES (CUST_COUNTRY, current_date(), CUR_USER, current_timestamp(), CUR_USER);
SELECT countryId FROM country WHERE country = CUST_COUNTRY
INTO ccountryId;
END;
END IF;
IF (SELECT cityId FROM city WHERE city = CUST_CITY AND countryId = ccountryId) IS NOT NULL THEN
BEGIN
SELECT cityId FROM city WHERE city = CUST_CITY AND countryId = ccountryId
INTO ccityId;
END;
ELSE BEGIN
INSERT INTO city (city, countryId, createDate, createdBy, lastUpdate, lastUpdateBy)
VALUES (CUST_CITY, ccountryId, current_date(), CUR_USER, current_timestamp(), CUR_USER);
SELECT cityId FROM city WHERE city = CUST_CITY AND countryId = ccountryId
INTO ccityId;
END;
END IF;
IF (SELECT addressId FROM address WHERE address = CUST_ADDRESS AND address2 = CUST_ADDRESS2 AND phone = CUST_PHONE AND cityId = ccityId) IS NOT NULL THEN
BEGIN
SELECT addressID FROM address WHERE address = CUST_ADDRESS AND address2 = CUST_ADDRESS2 AND phone = CUST_PHONE AND cityId = ccityID 
INTO caddressId;
END;
ELSE BEGIN
INSERT INTO address (address, address2, cityId, postalCode, phone, createDate, createdBy, lastUpdate, lastUpdateby)
VALUES (CUST_ADDRESS, CUST_ADDRESS2, ccityId, CUST_ZIP, CUST_PHONE, current_date(), CUR_USER, current_timestamp(), CUR_USER);
SELECT addressId FROM address WHERE address = CUST_ADDRESS AND address2 = CUST_ADDRESS2 AND phone = CUST_PHONE AND cityId = ccityID 
INTO caddressId;
END;
END IF;
UPDATE customer 
SET customerName = CUST_NAME, addressId = caddressId, active = 1, lastupdate = current_timestamp(), lastUpdateBy = CUR_USER
WHERE customerId = CUST_ID;
END;
ELSE BEGIN
SELECT * FROM customer WHERE customerId = CUST_ID;
END;
END IF;
END
IF (SELECT cityId FROM city WHERE city = CUST_CITY AND countryId = ccountryId) IS NOT NULL THEN 
BEGIN SELECT cityId FROM city WHERE city = CUST_CITY AND countryId = ccountryId INTO ccityId; 
END; 
ELSE BEGIN INSERT INTO city (city, countryId, createDate, createdBy, lastUpdate, lastUpdateBy) 
VALUES (CUST_CITY, ccountryId, current_date(), CUR_USER, current_timestamp(), CUR_USER); 
SELECT cityId FROM city WHERE city = CUST_CITY AND countryId = ccountryId INTO ccityId; 
END; 
END IF; 
IF (SELECT addressId FROM address WHERE address = CUST_ADDRESS AND address2 = CUST_ADDRESS2 AND phone = CUST_PHONE AND cityId = ccityId) IS NOT NULL THEN 
BEGIN SELECT addressID FROM address WHERE address = CUST_ADDRESS AND address2 = CUST_ADDRESS2 AND phone = CUST_PHONE AND cityId = ccityID INTO caddressId; 
END; 
ELSE 
BEGIN INSERT INTO address (address, address2, cityId, postalCode, phone, createDate, createdBy, lastUpdate, lastUpdateby) 
VALUES (CUST_ADDRESS, CUST_ADDRESS2, ccityId, CUST_ZIP, CUST_PHONE, current_date(), CUR_USER, current_timestamp(), CUR_USER); 
SELECT addressId FROM address WHERE address = CUST_ADDRESS AND address2 = CUST_ADDRESS2 AND phone = CUST_PHONE AND cityId = ccityID INTO caddressId; 
END; 
END IF; 
UPDATE customer 
SET customerName = CUST_NAME, addressId = caddressId, active = 1, lastupdate = current_timestamp(), lastUpdateBy = CUR_USER WHERE customerId = CUST_ID; 
END; 
ELSE 
BEGIN 
SELECT * FROM customer WHERE customerId = CUST_ID; 
END; 
END IF; 
END

Answer 1

To address your error, you should understand that when you use a subquery and compare the result to a single value, the subquery must return a scalar.

The mistake is here:

IF (SELECT * FROM customer WHERE customerId = CUST_ID) IS NOT NULL THEN

How can SELECT *, which I assume returns multiple columns, be in an `IS NOT NULL expression? Which of the many columns is tested for null?

SQL does support tuple comparisons like this:

(column1, column2, column3) = (1, 2, 3)

But some comparison operations don't support this format, for example LIKE or IS NOT NULL. For those, you must use just one column on the left of the comparison.

See https://dev.mysql.com/doc/refman/5.7/en/scalar-subqueries.html for more details on this.

It would be more clear to use EXISTS (subquery) instead of (subquery) IS NOT NULL.

IF EXISTS (SELECT * FROM ...) THEN
    ...

Then it doesn't matter what you put in the select-list, MySQL will ignore it anyway, since it's only interested in whether one or more rows exist, not what they return. See https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

---

Besides that error, I have some other comments on your code:

• You don't need so many BEGIN ... END blocks. The IF THEN ELSE END IF syntax already supports blocks of multiple statements. See https://dev.mysql.com/doc/refman/5.7/en/if.html

• Code indentation and formatting is important to help you spot logic mistakes.

• You seem to be using UPDATE as if it supports an ELSE clause. Maybe you mean "if it matches no rows, then do this other thing"? This is a mistake. There is no such feature for UPDATE.

  You might like to use the ROW_COUNT() function instead, to test if the UPDATE changed anything.

• Several times you use SELECT...INTO to capture an auto-increment ID that was just generated by a preceding INSERT. It would be simpler to do this:

  SET ccountryId = LAST_INSERT_ID();
  

  That function always returns the most recently generated auto-increment id, and it's safe to use if there are other concurrent sessions doing their own inserts. See the manual on the LAST_INSERT_ID() function to read about it.