Thanks for your response. I sort of figured I was out of luck. As it turned out, things weren't nearly as bad as they could be - I had turned on the SQL query echo to the log, so I had a complete trace of all the statements that had gone into creating that table. It took a few hours' work, but with some perl, grep, and manual editing I managed to recreate it.
Thanks, too, for the explanation of why the query worked as it did. Lessons learned: - When doing unusual things to the database, do them within a transaction - Do a formal backup more often ap ---------------------------------------------------------------------- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu On Wed, 30 Oct 2002, Tom Lane wrote: > Andrew Perrin <[EMAIL PROTECTED]> writes: > > I just made a mistake that could be quite costly: I did this: > > update writer_survey set partid='W41308' where survid in (select survid > > from participants where partid='W41291' limit 1); > > when I should have done this: > > update writer_survey set partid='W41308' where survid in (select survid > > from writer_survey where partid='W41291' limit 1); > > Ooops. > > > Is there any way I can undo this, e.g., set partid back to what it was > > before I ran this command? I know I should have done it in a transaction, > > but I didn't. > > You're probably out of luck. Got a recent backup? > > (If you were really desperate, and haven't yet vacuumed the table, you > could imagine manually changing the transaction's commit status in > pg_xlog and then clearing any known-committed status bits in the table. > But this is ticklish stuff and there are no tools for it that I know of.) > > > More broadly, can someone explain why it worked? There is no survid column > > in participants, so I would have expected it to generate an error on the > > sub-select, not match all rows! > > But the sub-select can reference the outer query's variables. So as > long as "from writer_survey where partid='W41291'" produced at least > one row, the sub-select would return the outer value of survid, and > thus the IN would succeed. > > regards, tom lane > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
