Member Avatar for srpa01red

i had a table categories(cid int primekey,name varchar,description text,parent_id,created date,updated date)
i want a script for insertion like this
insert into categories(...) values(cid value,name value,..desc, parent_Id..);
can i write the statement as
insert into categories(....) values (1,'aaa','some text',select parent_id from categories where parent_id=id,date,date);
just i wana know how can i write a select statement in insert statement
so that i can the column value through select statement.
in above querry i have to pick the parent value from cid.

  • 3 Contributors
  • 2 Replies
  • 103 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by saurav.prasad28

Dani AI

Generated

A couple of practical approaches will solve this. You cannot mix a VALUES(...) list and a table SELECT in the same INSERT — use either INSERT ... VALUES(...) (optionally with scalar subqueries inside the VALUES) or INSERT ... SELECT ... FROM .... Also be careful: parent_id = id is ambiguous; you must reference the actual column (for example cid) or a literal/parameter. was right to flag that.

If you want to insert a new row and take the parent id from an existing category row, use INSERT ... SELECT. Example:

INSERT INTO categories (cid, name, description, parent_id, created, updated)
SELECT 100, 'New Category', 'Some text', c.cid, CURRENT_DATE(), CURRENT_DATE()
FROM categories AS c
WHERE c.cid = 5;

If you prefer a single VALUES statement with a lookup, use a scalar subquery for the parent_id column (the subquery must return exactly one value):

INSERT INTO categories (cid, name, description, parent_id, created, updated)
VALUES (101, 'Leaf', 'desc', (SELECT cid FROM categories WHERE name = 'ParentName' LIMIT 1), NOW(), NOW());

Notes and cautions:

  • If the subquery returns no rows you will insert NULL (or get an error if the column is NOT NULL). Use LIMIT 1 or a UNIQUE condition to guarantee a single row.
  • If cid is AUTO_INCREMENT and you need the new row to reference itself as parent_id, insert first and then update using LAST_INSERT_ID() inside a transaction to avoid races.
  • Check for duplicate primary keys or referential constraints; consider INSERT IGNORE, ON DUPLICATE KEY UPDATE, or explicit checks if needed.

See MySQL docs for details on the two patterns: INSERT ... SELECT and Subqueries. 's post shows the INSERT ... SELECT pattern working; adapt the examples above to your real column names and constraints.

Recommended Answers

All 2 Replies

Member Avatar for urtrivedi

You can try following, but I am not able to understand what is id in "parent_id=id".

insert into categories(....) values select 1,'aaa','some text', parent_id date,date from categories where parent_id=id;

Are you going to execute above query in php or mysql directly?

Member Avatar for saurav.prasad28 
"Table"	"Create Table"
"categories1"	"CREATE TABLE `categories1` (
  `cid` int(11) NOT NULL,
  `name` varchar(30) default NULL,
  `parentid` int(11) default NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1"


"cid"	"name"	"parentid"
"1"	"A"	"22"
"2"	"B"	"23"


"Table"	"Create Table"
"categories2"	"CREATE TABLE `categories2` (
  `parentid` int(11) default NULL,
  `cid` int(11) default NULL,
  `Name` varchar(30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1"

"parentid"	"cid"	"Name"
"33"	                 \N	\N
"34"	                 \N	\N
"35"	                 \N	\N



insert into categories2 (cid, name, parentid) 
select '3', 'C', categories1.parentid from categories1 where categories1.cid = 2;


"parentid"	"cid"	"Name"
"33"	                 \N	\N
"34"	                 \N	\N
"35"	                 \N	\N
"23"	                 "3"	"C"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.