Insert and Select

[Bruce D]

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?

-bruce

[cainlevy]

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:

CREATE TEMPORARY TABLE Donations2 SELECT * FROM Donations WHERE
assignmentid = 1

Then insert rows back into Donations from Donations2.