Question 407430 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL query to UPDATE column with value in different column of different record possibly using subquery

I am very rusty on MYSQL and having trouble updating columns based on a different column in a different record. I’m thinking I need to do a subquery but would appreciate help in how to do this.

The table being queried is an aggregate of other tables where the letters represent the table and the number the id in that table. Here is a simplified version. I realize this structure seems a bit clunky but it can’t be changed.

  Posts
    id|text
    1|The Foo Fighters may be going on tour

    Comments
    
    (parentid is what the comment is on. If comment is on a previous
comment than grandparentid is what the previous comment referenced.
     
    id|parentid|grandparentid|text
    
    1|post-1|NULL|Who cares
    2|comment-1|post-1|What do you mean who cares. That's awesome.
    3|comment-2|NULL|Have they set dates?

There are a lot of records such as 3 where the grandparentid is blank though it can be figured out as it is the parent in turn of the parent comment(eg comment 2).

How can I update all of the grandparentid of Record 3 where we know commentid of the parent and the parent has a parent.

I believe it is something like the following but would appreciate any validation or correction as I don’t want to run the query and potentially mess up the data Thanks for any guidance or suggestions.

UPDATE comments `c` 
SET grandparentid = (SELECT parentid FROM comments `co` WHERE co.id = c.parentid)
WHERE grandparentid IS NULL

Edit

Above code with = NULL changed to IS NULL as per Wilmm gives MYSQL error

You can’t specify target table ‘c’ for update in FROM clause

thinking of trying following but worried it will mess up table. MYSQL says it can’t roll it back…

UPDATE comments `c`
    SET c.grandparentid = co.parentid
LEFT JOIN comments `co` ON co.id=c.parentid
    WHERE c.grandparentid IS NULL;

Edit:

Following Query runs without error but does not change any rows… Not sure what it is doing

UPDATE comments `c` 
LEFT JOIN comments `co` 
ON c.parentid = co.id 
SET c.grandparentid = co.parentid 
WHERE c.grandparentid IS NULL
Tags:

2

Answers


  1. 0

    Hi I think you need to try with an inner join update like so:

    update comments com
    set com.grandparentid = com_p.parentid
inner join comments com_p on com_p.id=com.parentid
    where com.grandparentid is null;
    Login or Signup to reply.
  2. 0

    You want to update a column (columnA) in one row with the value of another column (columnB) from a different row, and the rows are identified based on a condition.

    Example Scenario:
    Let’s say you have a table named my_table with columns id, columnA, and columnB. You want to update columnA for a specific record where id = 1, and the value to update it with should be taken from columnB of the record where id = 2.

    UPDATE my_table AS t1
SET t1.columnA = (
  SELECT t2.columnB 
  FROM my_table AS t2 
  WHERE t2.id = 2
)
WHERE t1.id = 1;

    Explanation:

    1. UPDATE my_table AS t1: This initiates the update on my_table, giving it an alias (t1).
    2. SET t1.columnA = (…): We want to set the value of columnA in t1.

    Subquery:

    • SELECT t2.columnB: We retrieve the value of columnB from a row in the
      same table but with a different condition.
    • FROM my_table AS t2: This creates a second instance of the same
      table, given the alias t2.
    • WHERE t2.id = 2: This condition identifies the record where id = 2
      (or any other condition you’re using to find the appropriate value).
    1. WHERE t1.id = 1: Finally, we specify the record that we want to update (where id = 1).

    This query updates columnA for the record where id = 1 with the value from columnB of the record where id = 2. You can modify the WHERE conditions as per your specific case.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search