0

I'm struggling to convert my select query into a update query, it has aliases..

Update pads set RemoveMeDate = '1999-01-01 00:00:00' where padid in (
SELECT old_table.padid
FROM `jules-fix-reasons`.`pads` AS old_table
JOIN `jules`.`pads` AS new_table ON old_table.padid = new_table.`PadID`
WHERE new_table.RemoveMeDate <> '2001-01-01 00:00:00'
AND old_table.RemoveMeDate = '2001-01-01 00:00:00')

I've tried removing the aliases, but that doesn't help :(

EDIT - Richard, screen shot alt text

Jules
  • 7,568
  • 14
  • 102
  • 186

1 Answers1

0
Update pads 
set RemoveMeDate = '1999-01-01 00:00:00' 
where padid in (SELECT padid FROM 
                ( SELECT old_table.padid 
                 FROM `jules-fix-reasons`.`pads` AS old_table JOIN 
                      `jules`.`pads` AS new_table ON old_table.padid = new_table.PadID
                 WHERE new_table.RemoveMeDate <> '2001-01-01 00:00:00' AND old_table.RemoveMeDate = '2001-01-01 00:00:00') a)
BoltClock
  • 700,868
  • 160
  • 1,392
  • 1,356
richard
  • 12,263
  • 23
  • 95
  • 151
  • That didn't work, even with the extra bracket. Did you miss something after a ? – Jules Jan 09 '11 at 09:31
  • Ooops, try that. Edited in place. – richard Jan 09 '11 at 09:39
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''jules-fix-reasons'.'pads' AS old_table JOIN 'jules'.'pads' AS new_table ON old_' at line 1 – Jules Jan 09 '11 at 09:41
  • Try that. Edited in place again. I don't have your tables, etc. so I can't check all the syntax... – richard Jan 09 '11 at 09:48
  • A is the alias for the subquery within the subquery I added. Basically I just wrapped your subquery in another subquery. It's a workaround for the way MySQL translates the SQL for update statements with subqueries. – richard Jan 09 '11 at 09:52
  • Hmmm, I've added a screen shot with your last query, not sure why it doesn't work. – Jules Jan 09 '11 at 09:59
  • @Jules: I fixed his answer, he was mixing up single quotes and backticks in the table names. Could you see if his solution works now? – BoltClock Jan 09 '11 at 10:02