# Multiple items in the where clause while updating

**URL:** <https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240>\
**Category:** Rails\
**Created:** [November 2, 2006, 11:42am UTC](https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240 "2006-11-02T11:42:43Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Prasanna\_kumar\_N](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/f04885/32.png) [@Prasanna\_kumar\_N](https://www.ruby-forum.com/u/Prasanna_kumar_N)\
**Post date:** [November 2, 2006, 11:42am UTC](https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240/1 "2006-11-02T11:42:43Z")

</div>

Hi

The following is a database table named friends.

±----±-----±-----±------+  
| sid | id | fid | ftype |  
±----±-----±-----±------+  
| 30 | 1 | 2 | F |  
| 31 | 1 | 3 | R |  
| 32 | 3 | 2 | F |  
| 33 | 3 | 4 | F |  
±----±-----±-----±------+

I want to update the ftype field based on id and fid.

I want to achive the following.

Update friends set ftype=‘F’ where id=1 and fid=3

Im using the following code…

# =========== f = Friend.find(:first, :conditions =\> “id = 1 and fid = 3”) f.ftype=‘F’ f.save

This errors out because the sql satement that gets generated will be

UPDATE friends SET `sid` = 31, `ftype` = ‘F’, `fid` = 3 WHERE id = 1

As you can see the where clause always takes just the id attribute and  
ignores others which will try to update muliple rows.

Is there a way to handle this situation?

Thanks  
Prasanna

---

<div class="post-metadata">

**Author:** ![Prasanna\_kumar\_N](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/f04885/32.png) [@Prasanna\_kumar\_N](https://www.ruby-forum.com/u/Prasanna_kumar_N)\
**Post date:** [November 2, 2006, 11:49am UTC](https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240/2 "2006-11-02T11:49:18Z")

</div>

Rails (or rather activerecord) assumes by default that tables have a  
primary key called id, whereas your id is obviously not that.

it looks like sid is your primary key, so you’d need to stick  
set\_primary\_key “sid”

in the declaration of your class

Fred

---

<div class="post-metadata">

**Author:** ![Prasanna\_kumar\_N](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/f04885/32.png) [@Prasanna\_kumar\_N](https://www.ruby-forum.com/u/Prasanna_kumar_N)\
**Post date:** [November 2, 2006, 11:57am UTC](https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240/3 "2006-11-02T11:57:04Z")

</div>

Gr8!!! that works, I spent the whole day trying various  
combinations.  
Thanks a lot Fred.

Thanks  
Prasanna

---

<div class="post-metadata">

**Author:** ![Prasanna\_kumar\_N](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/f04885/32.png) [@Prasanna\_kumar\_N](https://www.ruby-forum.com/u/Prasanna_kumar_N)\
**Post date:** [November 3, 2006, 7:48am UTC](https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240/4 "2006-11-03T07:48:32Z")

</div>

I have a similar problem…

I have the following table name friends

±----±-----±-----±------+  
| sid | id | fid | ftype |  
±----±-----±-----±------+  
| 3 | 1 | 5 | N |  
±----±-----±-----±------+  
| 4 | 1 | 2 | N |  
±----±-----±-----±------+  
| 5 | 2 | 3 | N |  
±----±-----±-----±------+

where sid is the primary key and I have achieved thsi by adding  
set\_primary\_key “sid” to the class declaration.

Now I’m trying to add a new row to the above table with the following  
code.

```
    f = Friend.new
    f.id=1
    f.fid=4
    f.ftype='N'
    f.save

```

The row added wil be of the form

±----±-----±-----±------+  
| sid | id | fid | ftype |  
±----±-----±-----±------+  
| 1 | NULL | 4 | N |  
±----±-----±-----±------+

This is because

INSERT INTO friends (`ftype`, `fid`, `sid`, `id`) VALUES(‘N’,4, 1, NULL)

The sid field should be automatically generated since it is a  
auto\_increment field.

it seems that the statement  
f.id=1  
is not taking effect.

How do I handle this situation.

Thanks  
Prasanna

---

<div class="post-metadata">

**Author:** ![Prasanna\_kumar\_N](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/f04885/32.png) [@Prasanna\_kumar\_N](https://www.ruby-forum.com/u/Prasanna_kumar_N)\
**Post date:** [November 3, 2006, 7:59am UTC](https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240/5 "2006-11-03T07:59:15Z")

</div>

Prasanna kumar Nagasamudram wrote:

> I have a similar problem…  
> f = Friend.new  
> f.id=1  
> f.fid=4  
> f.ftype=‘N’  
> f.save
> 
> The row added wil be of the form
> 
> ±----±-----±-----±------+  
> | sid | id | fid | ftype |  
> ±----±-----±-----±------+  
> | 1 | NULL | 4 | N |  
> ±----±-----±-----±------+

I believe ActiveRecord will always assume that your primary key is  
referenced by “id”. Even if the column name is different, the accessor  
method is still id.

If you are writing an application from scratch in rails, you should have  
no reason to step outside the rails conventions. So your primary key  
should be named “id”. Although you can make it work, you will have  
headaches if you continue down this path.

That said, you might try it this way:

f = Friend.new  
f[:id] = 1  
f.fid = 4  
f.ftype = ‘N’  
f.save

using object[:column\_name] maps directly to the named attribute rather  
than being proxied through a method generated by active record.

---

<div class="post-metadata">

**Author:** ![Prasanna\_kumar\_N](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/f04885/32.png) [@Prasanna\_kumar\_N](https://www.ruby-forum.com/u/Prasanna_kumar_N)\
**Post date:** [November 3, 2006, 8:29am UTC](https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240/6 "2006-11-03T08:29:33Z")

</div>

Thanks Alex…

I trying to follow the rails convention of having id as the primary key.  
But my requirement is to have a field named id which should not be  
unique

I have a table named users as follows

±—±---------±----------------------------±---------------+  
| id | login | name | location |  
±—±---------±----------------------------±---------------+  
| 1 | praskuma | Prasanna Kumar | Bangalore |  
| 2 | nagasapk | Nagasamudram Prasanna Kumar | Bangalore |  
| 3 | mannesp | Siva | Andhra Pradesh |  
| 4 | puranikp | Prashant Puranik | Karnataka |  
| 5 | ankamrb | Ankam Ramesh Babu | Badrachalam |  
±—±---------±----------------------------±---------------+

and friends table as follows

±----±-----±-----±------+  
| sid | id | fid | ftype |  
±----±-----±-----±------+  
| 1 | 1 | 4 | N |  
±----±-----±-----±------+  
| 2 | 1 | 3 | N |  
±----±-----±-----±------+  
| 3 | 2 | 1 | N |  
±----±-----±-----±------+  
| 4 | 2 | 3 | N |  
±----±-----±-----±------+

id and fid above are foriegn keys pointing to the id field of the users  
table.

and id field in the friends table can have repeaded values, so i cannot  
have id as the primary key in the friends table.

I just kept sid field as a primary key just for future use.

If you can think of an alternative way (reorganizing tables), please let  
me know.

Thanks a lot for solving my earlier issue  
Prasanna

---

<div class="post-metadata">

**Author:** ![Prasanna\_kumar\_N](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/f04885/32.png) [@Prasanna\_kumar\_N](https://www.ruby-forum.com/u/Prasanna_kumar_N)\
**Post date:** [November 7, 2006, 11:01am UTC](https://www.ruby-forum.com/t/multiple-items-in-the-where-clause-while-updating/74240/7 "2006-11-07T11:01:10Z")

</div>

On Nov 3, 2006, at 1:48 AM, Prasanna kumar Nagasamudram wrote:

> ```
> f.id=1
> 
> ```
> 
> is not taking effect.
> 
> How do I handle this situation.
> 
> Thanks  
> Prasanna

Your problem is that f.id=1 is “set the primary key to 1”.  
See this doc: [http://api.rubyonrails.org/classes/ActiveRecord/](http://api.rubyonrails.org/classes/ActiveRecord/)  
Base.html#M000901

I don’t know how you “fix” this in your situation, but knowing the  
cause ought to help.

-Rob

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid