insert if row count is 0

Question

The following query works in MySQL:

INSERT INTO `test`.`address_tab1`
(`city`,`street_name`,`pincode`)

SELECT
'mycity1', 'mystreerName1', '4855881'

where (SELECT COUNT(*) FROM `test`.`address_tab1`) = 0;

But the following query does not work, although there are no syntax error. Why so? I would like to insert multiple rows.

INSERT INTO `test`.`address_tab1`
(`city`,`street_name`,`pincode`)

SELECT
('mycity1', 'mystreerName1', '4855881')

where (SELECT COUNT(*) FROM `test`.`address_tab1`) = 0;

I get the following error:

Operand should contain 1 column(s)

Bill Karwin · Accepted Answer · 2023-05-31T06:51:51.907

When you make a list of expressions with parentheses, this is a tuple. But each item in a select-list must be a scalar expression; they can't be tuples.

Another way of thinking of this is that when using INSERT ... SELECT, you're inserting the results of the SELECT into some columns. They are matched up by position.

INSERT INTO `test`.`address_tab1` 
    (`city`,   `street_name`,   `pincode`)
     ^1        ^2               ^3
SELECT 
    'mycity1', 'mystreerName1', '4855881'
    ^1         ^2               ^3
where (SELECT COUNT(*) FROM `test`.`address_tab1`) = 0;

But if you tried to use a tuple in the select-list (even if it were supported, which it isn't) it would count as only one thing, so it would try to insert it into the first column of your address_tab1 table. But inserting a tuple into a single column is not legal, and it would also leave no values for the other two columns in the insert-list.

INSERT INTO `test`.`address_tab1` 
    (`city`,                                `street_name`, `pincode`)
     ^1                                     ^2?            ^3?
SELECT 
    ('mycity1', 'mystreerName1', '4855881')
    ^1                                      ^2?            ^3?
where (SELECT COUNT(*) FROM `test`.`address_tab1`) = 0;

I'm using these markers like ^1 only to indicate each column in these examples. These markers are not part of SQL syntax.

Re your comment:

I can use the above method to create separate queries for each record that I want to add to the table. Is there a way I can do it in one query.

I would use a new syntax of MySQL 8.0: the VALUES statement. This allows you to specify multiple rows of literal values.

INSERT INTO test.address_tab1 
SELECT * FROM (
  VALUES 
   ROW('mycity1', 'mystreetName1', '4855881'),
   ROW('mycity2', 'mystreetName2', '1885584'),
   ...
) AS t
WHERE (SELECT COUNT(*) FROM `test`.`address_tab1`) = 0;

If you are not using MySQL 8.0 yet, you should upgrade, because support for MySQL 5.x is ending soon.

I know there is an [old answer](https://stackoverflow.com/questions/15820288/mysql-error-1241-operand-should-contain-1-columns) that says you must remove the parentheses and not use a tuple, but it doesn't explain why this is required. I found other questions about MySQL error 1241, but none explained the reason. — Bill Karwin, May 30 '23 at 23:27
Thanks for your prompt response. I would like to add multiple records into a table if there are no records in the table. I can use the above method to create separate queries for each record that I want to add to the table. Is there a way I can do it in one query.? Hope you have understood my question. — Nazir, May 31 '23 at 04:29
INSERT INTO address_tab1 VALUES ROW('mycity1', 'mystreetName1', '4855881'), ROW('mycity2', 'mystreetName2', '1885584'), ...; When I tried to add where or if clause to check if there are no records in table, I get error - "where is not valid in this position" By the by, I am using MySQL 8.0.31. Thanks — Nazir, May 31 '23 at 06:45
Okay, I've edited to show how to use it with a WHERE clause. — Bill Karwin, May 31 '23 at 06:52

insert if row count is 0

1 Answers1