CODEWITHSUNDEEP
mysqlsqlsql-server
user3001167
user3001167

Reputation: 61

Update SQL query

I am writting a SQL query , where I need to update the colouns in dummy coloumn with the value on the basis of same post_id such that for post_id '1' all the rows will have 'abc' in there dummy coloumn.

Similarly for the post_id '2' all the rows will have 'def' in the coloumn under dummy .

post_id |meta_key|dummy
1         url      abc
1         link     Null
1         key      Null
1         data     Null
2         url      def
2         link     Null
2         key      Null
2         data     Null

The query I tried is : 

update <table_name>
  set dummy = ( select meta_value 
                       from <table_name> 
                       where meta_key = 'url' 
                       AND post_id = '1'
               ) 
  where post_id IN ('1') ;

This is working , but i want a dynamic query as i cant hardcode the post_id value as the data is like 10,000 post_ids.

The data after updating will look like this 

post_id|meta_key|dummy
1        url      abc
1        link     abc
1        key      abc
1        data     abc
2        url      def
2        link     def
2        key      def
2        data     def

Upvotes: 2

Views: 337

Answers (3)

Sunender kumar
Sunender kumar

Reputation: 21

update Table_Name 
set dummy = Case when post_id='1' then 'abc'
                 when post_id='2' then 'def'
end
where post_id in('1','2')

enter image description here

Upvotes: 2

Kahn
Kahn

Reputation: 1660

First I would check that each post_id really only has one value, so you don't end up doing an update based on a randomly selected value of several available:

SELECT post_id, COUNT(DISTINCT dummy) dummyCount
FROM TABLE
WHERE dummy IS NOT NULL
GROUP BY post_id
HAVING COUNT(DISTINCT dummy) > 1

If that has results, you should check them.

Then if there really is just one non-null dummy value per each post_id, you can update all the nulls as described in the OP like so:

UPDATE T
SET dummy = SRC.dummy
FROM TABLE T
JOIN 
   (SELECT post_id, dummy
   FROM TABLE
   WHERE dummy IS NOT NULL) SRC ON SRC.post_id = T.post_id
--WHERE dummy IS NOT NULL > should not be used
WHERE dummy IS NULL

Upvotes: 0

Damodaran
Damodaran

Reputation: 11057

You can use CASE

UPDATE <table_name> 
SET dummy = CASE post_id 
WHEN 1 THEN 'abc'
WHEN 2 THEN 'def'
END
WHERE post_id IN(1,2)

Due to the comment that posted , I am giving a sample code how to use this

If you have the "post_id" and corresponding "dummy" value in an array named "value". It is javascript code since I am using the node.js.

for(var key in values){
dummypart += ' WHEN '+values[key]['post_id']+' THEN '+values[key]['dummy'];
ids+= ','+ values[key]['post_id'];
}

And I generate the query as

var query = 'UPDATE <table_name> SET dummy = CASE post_id '+dummypart+'END 
WHERE post_id IN('+ids+')';

The exact query will be now 

UPDATE <table_name> 
    SET dummy = CASE post_id 
    WHEN 1 THEN 'abc'
    WHEN 2 THEN 'def'
    END
    WHERE post_id IN(1,2)

If the value array contains ids 1 and 2 ; and corresponding values. So in a single query I able to update multiple rows in a table. I do't know your exact requirement. But since you are trying to generate dynamic query, try this.

Upvotes: 0

Related Questions