MySQL update records of table with reference to columns of the same table

Question

I am using MySQL 5.6.17.

I have a self-referencing table TableA with columns id (PK), title (varchar), type (varchar), parent_id (FK that refers to id of the same table).

The sample data is as below :

id    title    type    parent_id
1     abc      G       NULL
2     def      G       NULL
3     xyz      G       NULL
4     pqr      G       NULL
5     abc      T       NULL
6     def      T       NULL
7     xyz      T       NULL
8     pqr      T       NULL

Now, I want each record having type='G' should become the child of the record with type='T' having the same title.

So the resultant table data should be :

id    title    type    parent_id
1     abc      G       5
2     def      G       6
3     xyz      G       7
4     pqr      G       8
5     abc      T       NULL
6     def      T       NULL
7     xyz      T       NULL
8     pqr      T       NULL

I've tried query below :

UPDATE TableA 
SET parent_id = (SELECT id FROM ( SELECT id FROM TableA WHERE TYPE='T' ) d)
WHERE TYPE='G';

But it returns

Error Code: 1242
Subquery returns more than 1 row

I also have tried :

UPDATE TableA t1
SET t1.parent_id = t2.newlocalid
INNER JOIN (
    SELECT title, id AS newlocalid
    FROM TableA t2 
    WHERE  TYPE='T' 
) t2 ON t1.title = t2.title
WHERE t1.type='G'

But it also returns the error in Syntax.

Can anyone help me to achieve it?

Forget about the UPDATE. Can you write a SELECT that returns the desired result set? — Strawberry, Nov 26 '14 at 11:05
I am novice using MySQL. I think I have missed something that's why I have asked the question here. Have you any feasible solution? — Dev, Nov 26 '14 at 11:08

score 1 · Accepted Answer · answered Nov 26 '14 at 11:13

1

UPDATE TABLEA a 
JOIN TABLEA b ON a.title = b.title and a.type='G'and b.type='T'
SET a.parent_id = b.id

answered Nov 26 '14 at 11:13

Amir Rahimi Farahani

1,580
1
12
14

score 1 · Answer 2 · answered Nov 26 '14 at 11:18

1

This should work:

SELECT title, id AS newlocalid
INTO   #t2
FROM TableA     
WHERE  TYPE='T' 


UPDATE t1
SET t1.parent_id = t2.newlocalid
FROM TableA as t1
INNER JOIN  #t2 as t2 
ON t1.title = t2.title
WHERE t1.type='G'

answered Nov 26 '14 at 11:18

Hamdi Baligh

874
1
11
30

Suchit kumar · Answer 3 · 2014-11-26T11:19:35.327

0

to update the table column first you have to identify which value to set. you can not use multiple values to update the single column in same statement.

change your sql to something like this:

UPDATE TableA 
SET parent_id = (SELECT id FROM TableA WHERE TYPE='T' limit 0,1)// i mean make sure that it is returning single record not multiple.or better add some more where condition to get a single and required record without using limit
WHERE TYPE='G';

or to some specific condition like this:

 UPDATE TableA 
    SET parent_id = (SELECT id FROM TableA aa WHERE TYPE='T' and aa.type=TableA.type)
    WHERE TYPE='G';

edited Nov 26 '14 at 11:19

answered Nov 26 '14 at 11:13

Suchit kumar

11,809
3
22
44

I think in this case I will not be sure that the title will be the same. That means, the record with type 'T' and title 'abc' must be the only parent of the record with type 'G' and title 'abc'. – Dev Nov 26 '14 at 11:19
in this case you can see this:http://stackoverflow.com/questions/15209414/mysql-update-join – Suchit kumar Nov 26 '14 at 11:22

score 0 · Answer 4 · answered Nov 26 '14 at 11:17

0

Try:

UPDATE TableA a
SET parent_id = (SELECT id FROM TableA ref WHERE TYPE='T' AND ref.title=a.title)
WHERE TYPE='G';

answered Nov 26 '14 at 11:17

Chris Halcrow

28,994
18
176
206

MySQL update records of table with reference to columns of the same table

4 Answers4