How can update directly from phpmyadmin column X

Question

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

possible duplicate of [SQL Insert into ... values ( SELECT ... FROM ... )](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) – You can combine an insert statement with a select statement. — feeela, Aug 13 '14 at 23:14
@feeela The OP is asking about `UPDATE`, not `INSERT` - you can't update a table in MySQL, that is used in a self-joined way inside the query view. — Eugen Rieck, Aug 14 '14 at 11:10

score 1 · Accepted Answer · answered Aug 13 '14 at 23:05

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
;

How can update directly from phpmyadmin column X

1 Answers1