[SOLVED] insert if row count is 0

insert if row count is 0

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)

Solution

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.