Advanced SQL UPDATE question

Question

I need to convert a table redeem to include a date that will be populated from blah, but it doesn't exist as of right now.

I need to SELECT all of the rows in the redeem table where pay autokey matches the redeem.pay. The blah.number is included in the pay table and in blah I want to get the blah.date. Then I need to set it to the redeem.date and make sure that the redeem.days_redeemed is not equal to 0.

UPDATE redeem
SET redeem.date =(SELECT blah.date
FROM blah
INNER JOIN pay ON pay.number = blah.number
INNER JOIN redeem ON redeem.pay = pay.autokey
WHERE redeem.days_redeemed <>0)

This is the error I receive: You can't specify target table 'redeem' for update in FROM clause

How do I go about doing this in MySQL in one SQL statement?

Let me get this straight you are trying to update a table that doesn't exist? If so that isn't possible. — Jared, Sep 19 '11 at 18:42
No, I am trying to create a field that doesn't exist yet using information from the blah table that already exists. — Chris, Sep 19 '11 at 18:55
This question might give yo a hint as to what to try: http://stackoverflow.com/questions/839938/mysql-sql-update-with-correlated-subquery-from-the-updated-table-itself — HLGEM, Sep 19 '11 at 19:15

ypercubeᵀᴹ · Accepted Answer · 2011-09-19T19:10:53.663

2

UPDATE
    redeem
  JOIN
    pay 
      ON pay.autokey = redeem.pay 
  JOIN
    blah
      ON blah.number = pay.number 
SET 
    redeem.date = blah.date
WHERE
    redeem.days_redeemed <> 0

This should work too:

UPDATE
    redeem
SET 
    date = 
      ( SELECT blah.date
        FROM 
            blah
          JOIN
            pay 
              ON blah.number = pay.number 
        WHERE 
            pay.autokey = redeem.pay 
      )
WHERE
    days_redeemed <> 0

edited Sep 19 '11 at 19:10

answered Sep 19 '11 at 19:04

ypercubeᵀᴹ

113,259
19
174
235

Ding ding we have a winner! Thanks ypercube, I got it to work with your second solution. – Chris Sep 19 '11 at 19:25

score 1 · Answer 2 · answered Sep 19 '11 at 18:42

1

try this:

UPDATE redeem SET redeem.date = blah.date
FROM blah
INNER JOIN pay ON pay.number = blah.number
INNER JOIN redeem ON redeem.pay = pay.autokey
WHERE redeem.days_redeemed <>0

answered Sep 19 '11 at 18:42

Natalia

311
1
8

MySQL doesn't like this at all. I get this error when I run the query:#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 'FROM blah INNER JOIN pay ON pay.number = blah.number INNER JOI' at line 3 – Chris Sep 19 '11 at 18:47

score 0 · Answer 3 · answered Sep 19 '11 at 18:45

If you wrap it in another sub-query (because of the way MySQL issues sub queries) it should work:

UPDATE redeem
SET redeem.date =(SELECT date from ( select * from
FROM blah
INNER JOIN pay ON pay.number = blah.number
INNER JOIN redeem ON redeem.pay = pay.autokey
WHERE redeem.days_redeemed <>0))

score 0 · Answer 4 · answered Sep 19 '11 at 18:47

You should use alias for the redeem table in FROM clause:

    UPDATE redeem
    SET redeem.date =(SELECT blah.date
    FROM blah
    INNER JOIN pay ON pay.number = blah.number
    INNER JOIN redeem r ON r.pay = pay.autokey
    WHERE r.days_redeemed <>0)

Advanced SQL UPDATE question

4 Answers4