homepage Welcome to WebmasterWorld Guest from 23.23.12.202
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
using subquery within an INSERT statement (with multiple rows)
can i insert multiple rows in one statement?
techtheatre

5+ Year Member



 
Msg#: 4047468 posted 9:51 am on Dec 22, 2009 (gmt 0)
So...it may simply not be possible, but I want to have an insert statement (or whatever) to insert new rows into my database with some info coming from another table. the problem is that this requires the insertion of multiple rows. I know I can do it one row at a time, but that is going to be VERY tedious (several thousand rows).

INSERT INTO PagePlugins
(PageName, PluginCode, PluginLabel)
VALUES
(
(SELECT DISTINCT PageName FROM PageData),'plug_weather','Local Weather'
)

I get the following error:
MySQl: #1242 - Subquery returns more than 1 row

 

whoisgregg

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4047468 posted 7:41 pm on Dec 22, 2009 (gmt 0)

Try this syntax instead:

INSERT INTO PagePlugins (PageName, PluginCode, PluginLabel)
SELECT DISTINCT PageName, 'plug_weather' AS PluginCode, 'Local Weather' AS PluginLabel
FROM PageData

techtheatre

5+ Year Member



 
Msg#: 4047468 posted 8:16 pm on Dec 22, 2009 (gmt 0)

Awesome. I had way over-simplified the query in my post, but after tweaking my real query with this new format it works perfectly. Thanks!

whoisgregg

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4047468 posted 8:35 pm on Dec 22, 2009 (gmt 0)

Glad I could help. :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved