I need to add 2 different words to a specific field in a table.
table is:
field1, field2 and field3
So first I need to select the records that I want to alter:
SELECT * FROM MyTable where field3 = 1
Suppose the result is 10 records.
I now have 2 variables var1 and var2
I now need to add var1 to 50% of field2 of the table and var2 to the other 50%
How can I do this?
Example:
table:
id | field2 | field3
-----------------------------
1 hello 1
2 hello2 1
String1 String2
Select * from mytable where field3 = 1
Will return 2 fields...
next...
Insert String1 into 50% of result and String2 into the other 50% of result on field2
Output should be:
id | field2 | field3
-----------------------------
1 String1 hello 1
2 String2 hello2 1
Get count rows for update.
select count(*) cnt
from MyTable
where field3 = 1;
and in php store result to variable $cnt, also make calculation for the middle point between ranges.
$middle = ceil($cnt/2);
then run following query (of course don't forget to escape a values for variables passed in sql):
update MyTable a,
(SELECT @rownum:=0) b
set field2 = concat(field2, if((@rownum := @rownum+1) > $middle, '$var1', '$var2'))
where field3 = 1;
Example below:
<?php
$conn = mysqli_connect('localhost', 'user', 'pass', 'dbtest');
$rs = mysqli_query($conn, "select count(*) cnt from MyTable where field3 = 1");
$row = mysqli_fetch_assoc($rs);
$cnt = $row['cnt'];
$middle = ceil($cnt/2);
$var1 = 'xxx';
$var2 = 'yyy';
mysqli_query(
$conn,
"update MyTable a,
(SELECT @rownum:=0) b
set field2 = concat(field2, if((@rownum := @rownum+1) > $middle, '$var1', '$var2'))
where field3 = 1
"
);
i do not know if this works for mysql, but i suggest something like this (ms sql server syntax):
update mytable
set field2 = case when ROW_NUMBER() over (order by id)%2 = 0
then 'var1' + field2
else 'var2' + field2
end
where field3 = 1
or if you do not wish to update your table but only select the values:
select field1
, case when ROW_NUMBER() over (order by 1d)%2 = 0
then 'var1' + field2
else 'var2' + field2
end as field2
, field3
where field3 = 1
it adds 'var1' to even rows and 'var2' to odd rows. replace 'var1' and 'var2' with your variables.
it works by taking the modulo 2 of each row number and thereby seeing if the row number of the result set is even or odd. it then puts in var1 or var2 depending on the row number being even or odd.