CODEWITHSUNDEEP
phpsqlitepdo
Satyajeet
Satyajeet

Reputation: 3

How to handle column default value insert in SQLIte?

Below is the table structure. Insert query and Error message.

Table Structure

CREATE TABLE d0000ke_c_name (
    c_name INTEGER PRIMARY KEY NOT NULL,
    value VARCHAR(255) ,
    sc_version INTEGER ,
    sc_state INTEGER 
);

Insert Query

$sql  = "INSERT INTO d0000kv_c_name SELECT 3 AS 'c_name','manash' AS 'value'   UNION SELECT 2,'ram' UNION SELECT 1,'rahim'";
$statement = $this->odb->prepare($sql);
if (!$statement) {
    echo "\nPDO::errorInfo():\n";
    print_r($this->odb->errorInfo());
}

$statement->execute();

Error message

PDO::errorInfo(): Array ( [0] => HY000 [1] => 1 [2] => table d0000kv_c_name has 4 columns but 2 values were supplied )

Upvotes: 0

Views: 425

Answers (1)

mario.van.zadel
mario.van.zadel

Reputation: 2949

Your select SELECT 3 AS 'c_name','manash' AS 'value' UNION SELECT 2,'ram' UNION SELECT 1,'rahim' return 2 columns but your table d0000ke_c_name has 4 columns.

You have to declare the to be filled fields in your INSERT INTO:

$sql  = "INSERT INTO d0000kv_c_name (c_name, value) SELECT 3 AS 'c_name','manash' AS 'value' UNION SELECT 2,'ram' UNION SELECT 1,'rahim'";

(In this case the fields sc_version and sc_state will get the default values.)

Or you should add the missing fields (e.g. "0, 0" for fields sc_version and sc_state) to your SELECT:

$sql  = "INSERT INTO d0000kv_c_name SELECT 3 AS 'c_name','manash' AS 'value', 0, 0 UNION SELECT 2, 'ram', 0, 0 UNION SELECT 1,'rahim', 0, 0";

Upvotes: 3

Related Questions