Mysql – How to stop script execution if insert value is null and set value if not null

caseexecMySQL

I have tried to use Case and SET NOEXEC ON but get the following errors:

Syntax Error: unexpected 'SET' (set)
Syntax Error: missing 'closing parenthesis'

INSERT INTO tag (table, repr, tag, value)
    SELECT 'product' AS table,
           @Id AS repr,
           'product_code' AS tag,
           CASE @Code       
             WHEN NOT null THEN @Code
             ELSE SET NOEXEC ON
           END AS value`

@Id and @Code are declared parameters in the script

Best Answer

SET NOEXEC ON is not MySQL syntax.

The thing to do is to avoid selecting it:

INSERT INTO tag (table, repr, tag, value)
    SELECT 'product' AS table,
            @Id AS repr,
            'product_code' AS tag,
            @Code AS value`        -- The WHERE will prevent nulls
        WHERE @Code IS NOT NULL;   -- avoid generating anything to insert when NULL

Related Solutions

Mysql query not working

Your problem is the quotes you are using for the default of NAME.

Here is your original create table from the question

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’, <<--- LOOK HERE
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You gave default ‘’ and the mysql client does not understand it.

You need to express it with standard single quotes like this : default ''

I just noticed that a_horse_with_no_name's comment already says this.

Don't change anything else. Leave all other quotes as is.

Give it a Try !!!

MySQL – Insert All Columns from One Table into Another

have you tried this?

CREATE TABLE t_cross_result AS
SELECT *
FROM 't_modul' CROSS JOIN 'sensors'

Best Answer

Related Solutions

Mysql query not working

MySQL – Insert All Columns from One Table into Another

Related Question