7/28/2023 0 Comments Update mysql record![]() However, of course, the second statement would only work if you were checking a different table for a value - as noted above, you can't perform an EXISTS check on the table you're updating inside the UPDATE statement. Alternately, if you cannot use IF as you are, then IF might be being interpreted as an alias for gallery_image in the UPDATE statement. Without the WHERE, the SELECT would interpret position as an alias for gallery_image. I'm not immediately acquainted with using an IF statement like you tried to in your second statement, inside an UPDATE statement (MySQL isn't my "first" language) if it is valid to structure things that way, then the problem may involve you leaving out the keyword WHERE in the SELECT statement. If you wrapped the statement in an EXISTS, then it may work in a stored proc. The IF was expecting a Boolean value, TRUE or FALSE - your first query gave it multiple rows with (I assume) multiple columns. SET := (SELECT COUNT(*) FROM gallery_image WHERE position = gallery_imageĪND position > a db-fiddle link, showing this works as you've said you want it to.Ī few additional notes on your two statements:Įven in a stored procedure, I don't think your first statement would work as written. I get an updated dump file from a remote server every 24 hours. I have one big table which contains around 10 millions + records. Server has 32GB RAM and is running Cent OS 7 圆4. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. If you don't want/need to create a stored procedure, you can capture the count of rows that meet your check, and only do the UPDATE if that count is greater than 0: SET = 8 Using MySQL 5.6 with InnoDB storage engine for most of the tables. ) within the UPDATE statement itself (as you were presumably trying to do in your second statement). ![]() You also cannot perform EXISTS (SELECT * FROM gallery_image. I believe that, in order to use flow control statements (IF, WHILE, etc.), as in your first statement, you have to be in a stored procedure.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |