Update SQL query

Question

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

Sunender kumar · Accepted Answer · 2017-06-22 10:36:58Z

2

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')

edited Jun 22, 2017 at 10:36

answered Jun 22, 2017 at 9:43

Sunender kumar

213 bronze badges

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

Comments

Damodaran · Accepted Answer · 2013-11-20 10:42:31Z

0

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.

edited Nov 20, 2013 at 10:42

answered Nov 20, 2013 at 9:27

Damodaran

11.1k10 gold badges63 silver badges84 bronze badges

5 Comments

Klas Lindbäck Over a year ago

A CASE with 10000 different post_id:s?

user3001167 Over a year ago

I cant hardcode the values , as i have post_id more than 10,000 .

Damodaran Over a year ago

@KlasLindbäck Lindbäck Read question. He mentioned "dynamic query".

Damodaran Over a year ago

No. You need to generate query in your application logic. I implemented this for updating hundreds of entries in a table with single query. I cannot use 100 queries to update the entries.It is one solution for this. –

Klas Lindbäck Over a year ago

@Damodaran I have read the question. Generating an UPDATE that contains a CASE with around 10000 WHEN clauses doesn't sound like a good solution.

NickyvV · Accepted Answer · 2013-11-20 14:20:48Z

0

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

edited Nov 20, 2013 at 14:20

NickyvV

1,7522 gold badges16 silver badges18 bronze badges

answered Nov 20, 2013 at 9:48

Kahn

1,6601 gold badge13 silver badges23 bronze badges

Collectives™ on Stack Overflow

Update SQL query

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related