update table column mysql

Question

I want to update some column's value so i used this simple query but it doesn't work, can anyone suggest anything please ?

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')

"it doesn't work" is not a problem description, why doesn't it work? Don't quote a table name as if it's a string literal. — HoneyBadger, Apr 12 '18 at 08:13
You should provide us a small dump of your table or at least the structure — Averias, Apr 12 '18 at 08:17
could you please add sample data and expected output, either in question or in sqlfiddle ? — Alpesh Jikadra, Apr 12 '18 at 08:29
Your code doesn't even make sense if you try to fix the logic you have presented. For instance, the subquery is likely to return multiple rows. I would suggest that you ask *another* question, with sample data, desired results, and an explanation of what you want to accomplish. — Gordon Linoff, Apr 12 '18 at 10:58

score 0 · Answer 1 · answered Apr 12 '18 at 08:26

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')

Alpesh Jikadra · Answer 2 · 2018-04-12T09:45:22.290

0

You can not update a table where inner query or sub query with same table names.

Here you are updating tableA where in same query you are using tableA to get the result

MySQL Error 1093 - Can't specify target table for update in FROM clause

Thanks

edited Apr 12 '18 at 09:45

answered Apr 12 '18 at 09:06

Alpesh Jikadra

1,692
3
18
38

update table column mysql

2 Answers2