CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

Updating a table with data from the same table

I'm trying to update the hash and parType columns in table path with data from the same table. I can up with the following sql from an answer here on SO, but the update seems to fail me.

What I'm trying to do here, is update all the rows where hash = 0 and parType = 0, with hash = the same rows id and parType = the type value of parent id of that row.

If you'll see my desired results you understand.

Can you help? Never mind my sql.

Sql

update path t join(
select t1.id, t1.type, t2.parent from path t1, path t2 where t1.parent = t2.id;
) as p on p.id=t.id set hash = t1.id and parType = t1.type;

Before Update

"id"    "type"  "parent"    "hash"  "parType"
"1"     "0"     "0"         "0"     "0"
"2"     "2"     "1"         "0"     "0"
"3"     "3"     "2"         "0"     "0"
"4"     "4"     "3"         "0"     "0"

Desired results

"id"    "type"  "parent"    "hash"  "parType" //parType = the type of the parent
"1"     "0"     "0"         "1"     "0"
"2"     "2"     "1"         "2"     "0"
"3"     "3"     "2"         "3"     "2" -> This is value from the parents type
"4"     "4"     "3"         "4"     "3"

Edit - This works, but I'm still not sure if it's the right way to do it

update path a join(
select t1.id as hash, t2.type as parType from path t1 inner join path t2 on t1.parent = t2.id
) b on a.id=b.hash set a.hash = b.hash , a.parType = b.parType;

Upvotes: 0

Views: 79

Answers (3)

Mihai
Mihai

Reputation: 26784

Something like this?

UPDATE table1 t LEFT JOIN
(SELECT t1.id,t1.type FROM table1 t1 INNER JOIN table1 t2 ON t1.id>t2.id GROUP BY t1.id) as p 
ON p.id+1=t.id SET t.hash=t.id,t.parType=ifnull(p.type,0)

SQL fiddle

Upvotes: 1

sarwar026
sarwar026

Reputation: 3821

Try with this please

update path t join(
select t1.id, t2.type, t2.parent from path t1, path t2 where t1.parent = t2.id;
              ^^
) as p on p.id=t.id set hash = t1.id and parType = t2.type;
                                                   ^^

I've changed the inner join from t1.type to t2.type and the ON condition from parType = t1.type to parType = t2.type

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146449

try this

update t set 
   hash = t.id,
   partype = p.type
from path t 
   left join path p
      on p.id = t.parent
where hash = 0 
   and parType = 0

Upvotes: 0

Related Questions