Insert and Select

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I'm trying to figure out a way to get this to work:

INSERT  INTO Donations( finderid, cardused )
(SELECT finderid, 1
FROM Donations
WHERE assignmentid = 1)

Basically, there are a lot of records in Donations.  The Donations table has
a unique, auto-increment id field (not displayed).  Here's what I have...
ID, FinderID, CardUsed, Assignmentid
1, 101, 0, 1
2, 106, 0, 2
3, 221, 0, 1

And this is what I'm trying to get to
1, 101, 0, 1
2, 106, 0, 2
3, 221, 0, 1
4, 101, 1, 1
5, 221, 1, 1

I get an error in MySQL if I try the above query...says "Donations" can't be
part of my "FROM" clause.

Any ideas?


Re: Insert and Select

What MySQL version are you running? I found this:

"Prior to MySQL 4.0.14, the target table of the INSERT statement cannot
appear in the FROM clause of the SELECT part of the query. This
limitation is lifted in 4.0.14."

I suppose another route would be to create a MySQL temporary table:

assignmentid = 1

Then insert rows back into Donations from Donations2.

Site Timeline