0

EDIT2: Solved Thanks all for fast reply, appreciate ur help. Specially to Mr Jeremy Smyth for the working solution.

I'm fairly new to sql and cant find a solution to make an update query. I have the following table

Table: order

id | cid | pid 
1  | 1   |  a1 
2  | 1   |  a2
3  | 2   |  a2
4  | 2   |  a3
5  | 2   |  a4

I want the cid of 2 to become 1, BUT not updating rows which have same pid i.e(id.2 & id.3). The result i want is:

id | cid | pid
1  | 1   |  a1
2  | 1   |  a2
3  | 2   |  a2
4  | '1' |  a3
5  | '1' |  a4

pseudo query example: UPDATE order SET cid=1 WHERE cid=2 AND 1.pid <> 2.pid;

EDIT1: not to confuse pid values with cid and id i changed them with 'a' in start. as suggested i'll not use order as table name. On update I simply dont want duplicate pid for cid Sorry for bad English.

  • What's `1.product_id` and `2.product_id`? It's not valid SQL syntax, and there are no `product_id` columns in your table (should that be `pid`?). – Barmar Sep 10 '13 at 16:46
  • Your question isn't very clear. It's hard to tell quite what you want, since you gave such a tiny sample of data, and your explanation is not very good. "same pid" as what? – Barmar Sep 10 '13 at 16:47
  • @Barmar the question is clear. same `pid` as `cid`. – meze Sep 10 '13 at 16:48
  • @meze I don't think that's what he means. I suspect he means "same pid as another row with cid = 2" – Barmar Sep 10 '13 at 16:50
  • Don't use reserved words for table names. IT WILL DRIVE YOU MAD! – Strawberry Sep 10 '13 at 16:52
  • @Barmar 1.product_id is for reference what is wanted it to be like to behave. The query is pseudo query –  Sep 10 '13 at 16:52

3 Answers3

2

I hope I understood you right:

UPDATE `order`
   SET cid = 1 
 WHERE cid = 2 
   AND cid <> pid

What do you think?

Please notice: ORDER is a reserved word, read more.

Mr. B.
  • 8,041
  • 14
  • 67
  • 117
  • I would prefer using `<>` over `!=`. But that personal. [See here](http://stackoverflow.com/questions/2066987/using-the-correct-or-preferable-not-equal-operator-in-mysql) for more information. – Rik Sep 10 '13 at 16:50
  • @Mr.Bombastic ur code did not worked. it updates cid from 2 into 1 for every field. i dont want 3rd row to be updated bcoz that will create duplicate item for cid=1 –  Sep 10 '13 at 17:06
1

I think you need something like this. 

UPDATE order SET cid=1 WHERE cid=2 AND cid <> pid;
Chamal
  • 1,439
  • 10
  • 15
  • ur code did not worked. it updates cid from 2 into 1 for every field. i dont want 3rd row to be updated bcoz that will create duplicate item for cid=1 –  Sep 10 '13 at 17:10
0

This can only be done in multiple steps (i.e. not a single UPDATE statement) in MySQL, because of the following points

Point 1: To get a list of rows that do not have the same pid as other rows, you would need to do a query before your update. For example:

SELECT id FROM `order` 
WHERE pid NOT IN (
   SELECT pid FROM `order`
   GROUP BY pid
   HAVING COUNT(*) > 1
)

That'll give you the list of IDs that don't share a pid with other rows. However we have to deal with Point 2, from http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html:

In general, you cannot modify a table and select from the same table in a subquery.

That means you can't use such a subquery in your UPDATE statement. You're going to have to use a staging table to store the pids and UPDATE based on that set.

For example, the following code creates a temporary table called badpids that contains all pids that appear multiple times in the orders table. Then, we execute the UPDATE, but only for rows that don't have a pid in the list of badpids:

CREATE TEMPORARY TABLE badpids (pid int);

INSERT INTO badpids
   SELECT pid FROM `order`
   GROUP BY pid
   HAVING COUNT(*) > 1;

UPDATE `order` SET cid = 1
WHERE cid= 2 
AND pid NOT IN (SELECT pid FROM badpids);
Jeremy Smyth
  • 23,270
  • 2
  • 52
  • 65
  • very much what i thought, but did not knew how to do. I haven't tried it. let me try and reply back. Yes –  Sep 10 '13 at 17:14
  • i'm so noob, ur asnwer is perfect, and thought me 1 thing i cant use select and update in same table. Besides ur code works as i wanted and u intended. There is one minor type semicolon missing in 2nd query which took me 7 tries to figure out why it was giving error. Thanks a lot :) –  Sep 10 '13 at 18:07
  • my friend also sugested me this query which works same as urs. update `order` set `cid`='1' WHERE `cid`='2' and `pid` not in (select pid from (select distinct(`pid`) from `order` where cid='1') as temp_tbl) –  Sep 10 '13 at 18:23
  • how do i mark this as answered? Got it, the 'tick' check on left side. –  Sep 10 '13 at 18:29