CODEWITHSUNDEEP
phpcodeignitercodeigniter-2
Erick Garve
Erick Garve

Reputation: 25

get value from subquery in CodeIgniter

the thing is that i don know if there is a way to get a value from a subquery over an update operation, the query that i'm trying to do looks like this, on a example code of my model :

$query = "UPDATE some_table SET value_1 = ((SELECT value_2 FROM other_table WHERE id = 2) + 1) WHERE id = 2";

$this->db->query($query);

i hope you can help me or at least let me know an alternative way to get this using the active record

PD i'm working with the CodeIgniter Framework

Upvotes: 0

Views: 197

Answers (2)

Chibueze Opata
Chibueze Opata

Reputation: 10034

The equivalent of 

UPDATE some_table SET value_1 = ((SELECT value_2 FROM other_table WHERE id = 2) + 1) WHERE id = 2

in ActiveRecord is probably:

$value_2 = $this->db->get_where('other_table', array('id' => 2))[0];
$data = array('value_1', $value_2 + 1);
$this->db->update('some_table', $data, array('id' => 2));

Upvotes: 1

João Paulo Cercal
João Paulo Cercal

Reputation: 763

I not working with Codeigniter, but try this:

$query = "UPDATE some_table st SET st.value_1 = ((SELECT ot.value_2 FROM other_table ot WHERE ot.id = 2) + 1) WHERE st.id = 2";

$this->db->query($query);

Upvotes: 0

Related Questions