CODEWITHSUNDEEP
mysqlselecttemp-tables
Ben Dauphinee
Ben Dauphinee

Reputation: 4181

MySQL trying to update a table from values stored in a temp table

I'm doing a select from a table to fill in the values to update in my permanent table. MySQL complains that "Unknown column 'rtufu.FollowUpDays' in 'field list'". What am I doing incorrectly?

UPDATE FollowUpTbl 
  SET 
    FollowUpDate = DATE_ADD(NOW(), INTERVAL (rtufu.FollowUpDays) DAY) 
  WHERE FollowUpID IN (SELECT FollowUpID FROM RowsToUpdateFollowUps rtufu);

RowsToUpdateFollowUps (Temporary)
+------------+--------------+
| FollowUpID | FollowUpDays |
+------------+--------------+
|      64417 |           90 |
|      45508 |           90 |
+------------+--------------+

FollowUpTbl
+--------------+------------+
| FollowUpDate | FollowUpID |
+--------------+------------+
| 0000-00-00   |          1 |
| 0000-00-00   |          2 |
+--------------+------------+

Upvotes: 0

Views: 148

Answers (4)

Ben Dauphinee
Ben Dauphinee

Reputation: 4181

Inner joins can be used with updates.

UPDATE FollowUpTbl f 
INNER JOIN RowsToUpdateFollowUps rtufu ON rtufu.FollowUpID = f.FollowUpID 
SET f.FollowUpDate = DATE_ADD(
    NOW(), 
    INTERVAL (rtufu.FollowUpDays) DAY
);

Upvotes: 0

Danosaure
Danosaure

Reputation: 3655

Try this:

update FollowUpTbl as f
    inner join RowsToUpdateFollowUps as t using (FollowUpID)
set f.FollowUpDate = DATE_ADD(NOW(), interval t.FollowUpDays DAY);

Upvotes: 1

Aaron Scruggs
Aaron Scruggs

Reputation: 712

rtufu is defined in the 'inner' (aka nested) select. It does not have visibility in the 'outer' update.

Upvotes: 0

grahamparks
grahamparks

Reputation: 16296

Your query syntax is a little mangled. Try this.

UPDATE FollowUpTbl 
  SET 
    FollowUpDate = (
      SELECT
          DATE_ADD(NOW(), INTERVAL (rtufu.FollowUpDays) DAY)
          FROM RowsToUpdateFollowUps rtufu
          WHERE rtufu.FollowUpID = FollowUpTbl.FollowUpID
    );

Upvotes: 0

Related Questions