MySQL更新表字段语句失效求助：执行指定UPDATE语句无效果求解决方案

Hey there, let's break down why your UPDATE statement isn't doing anything and fix it step by step:

1. Fix the Critical Syntax Error #

First off, you wrapped tableA in single quotes ('tableA')—but in MySQL, single quotes are for string literals, not table names. When you write UPDATE 'tableA', MySQL treats that string as a literal instead of your actual table, so no rows get touched at all.

Fix it by using backticks (or skip them if your table name has no special characters):

UPDATE `tableA` 
SET post_parent = (SELECT wp.ID FROM `tableA` wp WHERE wp.post_content='' AND wp.post_name='ba' AND wp.post_type='pa') 
WHERE ID IN (SELECT ID FROM `tableA` WHERE post_name like '%ba-%' AND post_type='pa')

2. Verify Your Subqueries Return Results #

Even with the syntax fixed, if either subquery returns no rows, your update won't affect any records. Let's test them individually:

• Check the parent ID subquery: Run this alone to confirm it returns exactly one ID (multiple rows would throw an error, but zero rows would set post_parent to NULL or leave it unchanged if that's already the value):

  SELECT wp.ID FROM `tableA` wp WHERE wp.post_content='' AND wp.post_name='ba' AND wp.post_type='pa'
  

  If nothing comes back, double-check your conditions—maybe post_content isn't truly empty, or post_name has a typo (watch out for case sensitivity if your collation is case-sensitive).

• Check the target rows subquery: Run this to make sure there are rows matching your filter:

  SELECT ID FROM `tableA` WHERE post_name like '%ba-%' AND post_type='pa'
  

  If no rows show up, adjust your LIKE pattern or verify the post_type value is exactly correct.

3. Skip Unnecessary Updates (and Confirm Changes) #

Add a condition to skip rows that already have the correct post_parent value. This makes your update more efficient, and also helps you confirm if rows were actually modified:

UPDATE `tableA` 
SET post_parent = (SELECT wp.ID FROM `tableA` wp WHERE wp.post_content='' AND wp.post_name='ba' AND wp.post_type='pa') 
WHERE ID IN (SELECT ID FROM `tableA` WHERE post_name like '%ba-%' AND post_type='pa')
AND post_parent != (SELECT wp.ID FROM `tableA` wp WHERE wp.post_content='' AND wp.post_name='ba' AND wp.post_type='pa')

4. Rewrite with JOIN for Better Clarity #

Subquery-based updates can sometimes have hidden edge cases in MySQL. Rewriting using a JOIN makes the logic more explicit and often more reliable:

UPDATE `tableA` target
JOIN `tableA` parent 
  ON parent.post_content = '' 
  AND parent.post_name = 'ba' 
  AND parent.post_type = 'pa'
SET target.post_parent = parent.ID
WHERE target.post_name LIKE '%ba-%' 
  AND target.post_type = 'pa'
  AND target.post_parent != parent.ID; -- Optional: Skip already correct rows

After making these changes, run the update again and check the number of affected rows MySQL reports. If it's still zero, go back and double-check your subquery conditions—small typos or collation mismatches are often the sneaky culprits!

内容的提问来源于stack exchange，提问作者Chris Tseng