Mysql Update based on value and select query

Question

I want to make a UPDATE on tb_usermeta using one query, having meta_id, meta_key's and meta_value's. This is possible?

tb_usermeta:

+----+---------+-----------+------------+
| id | meta_id | meta_key  | meta_value |
+----+---------+-----------+------------+
|  1 |       1 | user_nome | user 1     |
|  2 |       1 | user_fone | 99999      |
|  3 |       2 | user_nome | user 2     |
|  4 |       2 | user_fone | 88888      |
+----+---------+-----------+------------+

Ex: Data:
meta_id = 1, meta_key = user_nome, meta_value = user changed
meta_id = 1, meta_key = user_fone, meta_value = 696969

tb_usermeta after update:

 +----+---------+-----------+-------------+
 | id | meta_id | meta_key  | meta_value  |
 +----+---------+-----------+-------------+
 |  1 |       1 | user_nome | user changed|
 |  2 |       1 | user_fone | 696969      |
 |  3 |       2 | user_nome | user 2      |
 |  4 |       2 | user_fone | 88888       |
 +----+---------+-----------+-------------+

Strawberry · Accepted Answer · 2017-06-28 17:02:27Z

1

Consider the following...

DROP TABLE IF EXISTS eav;

CREATE TABLE eav
(entity INT NOT NULL
,attribute VARCHAR(12) NOT NULL
,value  VARCHAR(20) NOT NULL
,PRIMARY KEY(entity,attribute)
);

INSERT INTO eav VALUES
(1,'user_nome','user 1'),
(1,'user_fone','99999'),
(2,'user_nome','user 2'),
(2,'user_fone','88888');

UPDATE eav 
   SET value = CASE attribute WHEN 'user_nome' THEN 'user changed' 
                              WHEN 'user_fone' THEN '696969' 
                              END 
 WHERE entity = 1;

SELECT * FROM eav;
+--------+-----------+--------------+
| entity | attribute | value        |
+--------+-----------+--------------+
|      1 | user_nome | user changed |
|      1 | user_fone | 696969       |
|      2 | user_nome | user 2       |
|      2 | user_fone | 88888        |
+--------+-----------+--------------+

It would be nice to do this with a view - but I think MySQL would consider the fact that you're updating two things at once 'an update to multiple underlying tables', which is not allowed.

answered Jun 28, 2017 at 17:02

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rafael Umbelino Over a year ago

Thanks for the help!

Collectives™ on Stack Overflow

Mysql Update based on value and select query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related