MySQL update from select

Question

Having trouble with this query:

UPDATE table1 t1 SET t1.custom_id=(SELECT t2.custom_id 
FROM table2 t2 WHERE LOWER(t1.first_name)=LOWER(t2.first_name) 
AND LOWER(t1.last_name)=LOWER(t2.last_name) 
AND (t2.zip1 LIKE CONCAT('%',t1.zip,'%') 
OR t2.zip2 LIKE CONCAT('%',t1.zip,'%')) LIMIT 1)

I triple-checked that php variables match the field names. When I split it into multiple queries update works fine.

variable is coming from where? / errors are what? is it executed? not enough code/information here IMHO and the schemas — Funk Forty Niner, Mar 31 '17 at 15:06
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Mar 31 '17 at 15:12
table1 is created from CSV and these variables are created from field names. Then I need to run this update. It's executed and no errors. — Ilya I, Mar 31 '17 at 15:13
well I for one fail to see how you are checking for (possible) errors and what the csv is. — Funk Forty Niner, Mar 31 '17 at 15:13
question's too unclear for me. you'll have to wait for someone else then because I can't go and setup a csv/tables for this. You'll need to provide a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). — Funk Forty Niner, Mar 31 '17 at 15:16
Is that the proper way of evaluating the variables? (I understand you want to return the column names in place) I assume that when you test in separate requests you're not capable of reproducing this situation, but that you directly use the column names... — St3an, Mar 31 '17 at 15:16
run an echo/var_dump and you'll see what's going through your query (or not). — Funk Forty Niner, Mar 31 '17 at 15:17
*"When I split it into multiple queries update works fine."* - Then my "guess" is; you're trying to run multiple queries with php but didn't use `mysqli_multi_query()` http://php.net/manual/en/mysqli.multi-query.php - If it works in phpmyadmin but not in php (as tagged) then something failed you; check for errors. That's the best I can add to all this, and the db schemas are unknown. — Funk Forty Niner, Mar 31 '17 at 15:26

score 0 · Answer 1 · edited May 23 '17 at 12:09

You could have tried searching StackOIverflow for an answer first, but in your case something like this should work:

 UPDATE table1 t1
 join table2 t2 on
 LOWER (t1.first_name) = LOWER (t2.first_name)
        AND LOWER (t1.last_name) = LOWER (t2.last_name)
         AND (   t2.zip1 LIKE CONCAT ('%', t1.zip, '%')
             OR t2.zip2 LIKE CONCAT ('%', t1.zip, '%')) LIMIT 1)
 SET custom_id = t2.custom_id

MySQL update from select

1 Answers1