CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Hayday Wxy
Hayday Wxy

Reputation: 17

How can update directly from phpmyadmin column X

I have columns A, B, C, D, and X. X should be calculated and entered in the database. I want to update column X in phpmyadmin.

select last.id, ( IF(last.A IN(prev.A, prev.B, prev.C, prev.D), 1, 0) + IF(last.B IN(prev.A, prev.B, prev.C, prev.D), 1, 0) + IF(last.C IN(prev.A, prev.B, prev.C, prev.D), 1, 0) + IF(last.D IN(prev.A, prev.B, prev.C, prev.D), 1, 0) ) as X FROMtabAS last LEFT JOINtabAS prev ONprev.id =last.id-1

Upvotes: 1

Views: 152

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65304

PHPmyAdmin has nothing to do with that: What you want is an SQL solution, using any medium to throw it against the database. PHPmyAdmin might be one of those.

That said, you already have your building blocks in place. Since MySQL doesn't allow updating a table, that is self-joined in a query view, the easiest way is to go via a temporary table:

-- First step: Create X
CREATE TABLE temp_tab SELECT 
  -- your query here
;

--  second step: Propagate to your table
UPDATE tab 
INNER JOIN temp_tab ON tab.id=temp_tab.id
SET tab.X=temp_tab.X
;

-- third step: clean up
DROP TABLE temp_tab
;

Upvotes: 1

Related Questions