CODEWITHSUNDEEP
mysqlsql
Alex
Alex

Reputation: 27

How to update value in same table using mysql?

I need to replace/update values in my table according att_id for each customer_id. The table looks like: 

ID att_id customer_id value
1  5            1     name
2  30           1     12345
3  40           1      
4  5            2     name2
5  30           2     12345
6  40           2

I'd like to replace it like this:

ID att_id customer_id value
1  5            1     name
2  30           1     
3  40           1     12345 
4  5            2     name2
5  30           2     
6  40           2     12345

Upvotes: 0

Views: 88

Answers (4)

Madan Ram
Madan Ram

Reputation: 876

UPDATE tableName SET value=12345 WHERE ID=2;
UPDATE tableName SET value="" WHERE ID=3;
UPDATE tableName SET value=12345 WHERE ID=6;
UPDATE tableName SET value="" WHERE ID=5;

This are the command .See http://www.tutorialspoint.com/mysql/mysql-update-query.htm for tutorial on update.

Upvotes: 0

peterm
peterm

Reputation: 92845

UPDATE: Based on your comments ...I need to find values for attribute 30, check if they are mobile phone numbers, and if it's true, write it itno value for attribute 40... your query might look like this

UPDATE table1 t1 JOIN table1 t2
    ON t1.customer_id = t2.customer_id
   AND t1.att_id = 40 
   AND t2.att_id = 30
   SET t1.value = t2.value
  --  ,t2.value = NULL -- uncomment if you need to clear values in att_id = 30 at the same time
 WHERE t2.value REGEXP '^[+]?[0-9]+$'

You might need to tweak a regexp to match your records ("mobile phone numbers") properly

Here is SQLFiddle demo

It's hard to tell for sure from your description but if you need to swap values of att_id 30 and 40 per customer_id you may do something like this

UPDATE table1 t1 JOIN table1 t2
    ON t1.customer_id = t2.customer_id
   AND t1.att_id = 40 
   AND t2.att_id = 30
   SET t1.value = t2.value,
       t2.value = t1.value

Here is SQLFiddle demo

or if you need to put values of att_id = 30 to att_id = 40 and "clear" values of att_id = 30

UPDATE table1 t1 JOIN table1 t2
    ON t1.customer_id = t2.customer_id
   AND t1.att_id = 40 
   AND t2.att_id = 30
   SET t1.value = t2.value,
       t2.value = NULL

Here is SQLFiddle demo

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Here is a general approach for swapping the values on rows with att_id equal to 30 and 40:

update t join
       t t30
       on t.customer_Id = t30.customer_Id and t30.att_id = 30 join
       t t40
       on t.customer_Id = t40.customer_Id and t40.att_id = 40 join
    set t.value = (case when att_id = 30 then t40.value 
                        when att_id = 40 then t30.value 
                        else t.value
                   end)
    where att_id in (30, 40);

Upvotes: 1

Melvin
Melvin

Reputation: 6008

First, you remove the value of att_id = 30

UPDATE tablename SET value="" WHERE att_id=30;

Then set the value for att_id=40

UPDATE tablename SET value="12345" WHERE att_id=40;

Upvotes: 0

Related Questions