1

I'm trying to update several rows with email addresses. T1.email is to be updated with T2.email based on T1.name existing in T2.name.

Currently, the query looks like this.

UPDATE T1
SET T1.email = T2.email
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.name = T2.name
WHERE T1.name = T2.name
AND (Some conditions)
LIMIT 1398

A similiar question is asked here, but a syntax error is given. SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?

I've also tried updating with ANY.

UPDATE Table1
SET Table1.email = ANY
    (
        SELECT Table2.email FROM Table2 
        WHERE Table1.accountid = 901234 
        AND Table2.pid = 123 
        AND Table2.email IS NOT NULL 
    )

WHERE Table1.name IN
    (
        SELECT Table2.name FROM Table2
        WHERE Table1.accountid = 19574
        AND Table2.pid = 123 
        AND Table2.email IS NOT NULL
    ) 

LIMIT 1398

Currently, this returns an error "SQL Error (1242): Subquery returns more than 1 row".

May be the beginning of a copy and paste job!

Community
  • 1
  • 1
parseMaestro63
  • 33
  • 1
  • 7

1 Answers1

1

As detailed under UPDATE Syntax, for MySQL you want:

UPDATE Table1 T1 JOIN Table2 T2 USING (name)
SET    T1.email = T2.email
WHERE  (Some conditions)
LIMIT  1398
eggyal
  • 122,705
  • 18
  • 212
  • 237