Allow Only 3 Rows to Be Added to a Table for a Specific Value

Could I make a column in a table only allows one 'true' value and all other rows should be 'false'

Rather than having the boolean attribute in the table, you could have another table that contains one row and points to the row in the original table that you consider true.

Changing the true value is a matter of updating the foreign key in the TrueRow table.

Adding new rows with new values at some specific columns in pandas

This assumes that the date restarts at 1990-01-01 for each id:

import itertools
# reindex to get all combinations of ids and week numbers
df_full = (df.set_index(["id", "week_num"])
             .reindex(list(itertools.product([1,2], range(1, 11))))
             .reset_index())
# fill people with zero
df_full = df_full.fillna({"people": 0})
# forward fill some other columns
cols_ffill = ["level", "a", "b"]
df_full[cols_ffill] = df_full[cols_ffill].ffill()
# reconstruct date from week starting from 1990-01-01 for each id
df_full["date"] = pd.to_datetime("1990-01-01") + (df_full.week_num - 1) * pd.Timedelta("1w")
df_full

# out:
    id  week_num  people       date  level    a    b
0    1         1    20.0 1990-01-01    1.0  2.0  3.0
1    1         2    30.0 1990-01-08    1.0  2.0  3.0
2    1         3    40.0 1990-01-15    1.0  2.0  3.0
3    1         4     0.0 1990-01-22    1.0  2.0  3.0
4    1         5   100.0 1990-01-29    1.0  2.0  3.0
5    1         6     0.0 1990-02-05    1.0  2.0  3.0
6    1         7   100.0 1990-02-12    1.0  2.0  3.0
7    1         8     0.0 1990-02-19    1.0  2.0  3.0
8    1         9     0.0 1990-02-26    1.0  2.0  3.0
9    1        10     0.0 1990-03-05    1.0  2.0  3.0
10   2         1     0.0 1990-01-01    1.0  2.0  3.0
11   2         2     0.0 1990-01-08    1.0  2.0  3.0
12   2         3     0.0 1990-01-15    1.0  2.0  3.0
13   2         4     0.0 1990-01-22    1.0  2.0  3.0
14   2         5     0.0 1990-01-29    1.0  2.0  3.0
15   2         6     0.0 1990-02-05    1.0  2.0  3.0
16   2         7     0.0 1990-02-12    1.0  2.0  3.0
17   2         8     0.0 1990-02-19    1.0  2.0  3.0
18   2         9     0.0 1990-02-26    1.0  2.0  3.0
19   2        10     0.0 1990-03-05    1.0  2.0  3.0

SQL Server Maximum rows that can be inserted in a single insert statment

The Maximum number of rows you can insert in one statement is 1000 when using INSERT INTO ... VALUES... i.e.

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3),...... upto 1000 rows. 

But if your are using a SELECT statement to insert rows in a table, there is no limit for that, something like...

INSERT INTO TableName (ColName)
Select Col FROM AnotherTable

Now coming to your second question. What happens when an error occurs during an insert.

Well if you are inserting rows using multi-value construct

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3)

In the above scenario if any row insert causes an error the whole statement will be rolled back and none of the rows will be inserted.

But if you were inserting rows with a separate statement for each row i.e. ...

INSERT INTO TableName( Colum1) VALUES (1)
INSERT INTO TableName( Colum1) VALUES (2)
INSERT INTO TableName( Colum1) VALUES (3)

In the above case each row insert is a separate statement and if any row insert caused an error only that specific insert statement will be rolled back the rest will be successfully inserted.

SQL select some rows in a table so that they sum up to certain value

Let's put it this way: If SQL was a religion I'd go to hell for providing this solution. SQL is not meant to solve this kind of problems, so any solution would be horrible. Mine is no exception :)

set @limitValue := 50;
select id, newQty1, newQty2, newQty3, newQty4 from (
  select id,
  if(@limitValue - qty1 > 0, qty1, greatest(@limitValue, 0)) newQty1,
  @limitValue := @limitValue - qty1 Total1,
  if(@limitValue - qty2 > 0, qty2, greatest(@limitValue, 0)) newQty2,
  @limitValue := @limitValue - qty2 Total2,
  if(@limitValue - qty3 > 0, qty3, greatest(@limitValue, 0)) newQty3,
  @limitValue := @limitValue - qty3 Total3,
  if(@limitValue - qty4 > 0, qty4, greatest(@limitValue, 0)) newQty4,
  @limitValue := @limitValue - qty4 Total4
  from (
    select id, qty1, qty2, qty3, qty4,
      @rowTotal < @limitValue Useful,
      @previousRowTotal := @rowTotal PreviousRowTotal,
      @rowTotal := @rowTotal + qty1 + qty2 + qty3 + qty4 AllRowsTotal,
      @rowTotal - @previousRowTotal CurrentRowTotal 
    from t,
    (select @rowTotal := 0, @previousRowTotal := 0) S1
  ) MarkedUseful
  where useful = 1
) Final

For the provided data, this results in:

+----+---------+---------+---------+---------+
| ID | NEWQTY1 | NEWQTY2 | NEWQTY3 | NEWQTY4 |
+----+---------+---------+---------+---------+
|  1 | 0       |       0 | 10      |      20 |
|  2 | 1.5     |       0 | 7.5     |      11 |
+----+---------+---------+---------+---------+

And the complement:

set @limitValue := 50;
select t1.id,
  coalesce(t1.qty1 - newQty1, t1.qty1) newQty1,
  coalesce(t1.qty2 - newQty2, t1.qty2) newQty2,
  coalesce(t1.qty3 - newQty3, t1.qty3) newQty3,
  coalesce(t1.qty4 - newQty4, t1.qty4) newQty4
from t t1 left join (
    select id,
    if(@limitValue - qty1 > 0, qty1, greatest(@limitValue, 0)) newQty1,
    @limitValue := @limitValue - qty1 Total1,
    if(@limitValue - qty2 > 0, qty2, greatest(@limitValue, 0)) newQty2,
    @limitValue := @limitValue - qty2 Total2,
    if(@limitValue - qty3 > 0, qty3, greatest(@limitValue, 0)) newQty3,
    @limitValue := @limitValue - qty3 Total3,
    if(@limitValue - qty4 > 0, qty4, greatest(@limitValue, 0)) newQty4,
    @limitValue := @limitValue - qty4 Total4
    from (
      select id, qty1, qty2, qty3, qty4,
        @rowTotal < @limitValue Useful,
        @previousRowTotal := @rowTotal PreviousRowTotal,
        @rowTotal := @rowTotal + qty1 + qty2 + qty3 + qty4 AllRowsTotal,
        @rowTotal - @previousRowTotal CurrentRowTotal 
      from t,
      (select @rowTotal := 0, @previousRowTotal := 0) S1
    ) MarkedUseful
    where useful = 1
) Final
on t1.id = final.id
where Total1 < 0 or Total2 < 0 or Total3 < 0 or Total4 < 0 or final.id is null

For the provided data, this results in:

+----+---------+---------+---------+---------+
| ID | NEWQTY1 | NEWQTY2 | NEWQTY3 | NEWQTY4 |
+----+---------+---------+---------+---------+
|  2 |       0 | 0       | 0       |       7 |
|  3 |       1 | 2       | 7.5     |      18 |
|  4 |       0 | 0.5     | 5       |      13 |
+----+---------+---------+---------+---------+

Enjoy!

Limit the insertions to MySQL table to N rows

You can use stored procedure instead of regular INSERT statement. The stored procedure will first add the row based on the input parameters and then delete old rows if required. Please see below example. You will probably have to adjust the input parameters for limitedInsert procedure based on your table's columns.

My example table schema

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `value` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Procedure

DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `limitedInsert`(
    IN `parameter_1` INT
)
BEGIN
    DECLARE rowsNumber INT;
    DECLARE idToRemove INT;

    insert into test(value) values(parameter_1);
    select count(*) into rowsNumber from test;

    if rowsNumber > 30 then
        select id into idToRemove from test order by timestamp asc limit 1;
        delete from test where id = idToRemove;
    end if;
END//
DELIMITER ;

Insert data using stored procedure

call limitedInsert(2);

Limit number of dynamically added Table Rows using JQuery

You could always simply use a variable for each table to track the number of added rows.

Or another alternative would be to use jQuery's data() method which allows you to store data in a dom element directly. The click event would find its table, and update the table's data() each time a row is added, and prevent further additions when it reaches the maximum.

EDIT: Corrected code with check to see if theCount is undefined, and to remove a mis-placed parentheses.

$('#Clone').click(function() {
    var $btn = $(this).parent();
    if($btn.closest('table').data('theCount') == undefined) {
        $btn.closest('table').data('theCount', 0)
    }
    var currentCount = $btn.closest('table').data('theCount');

    if(currentCount < 5) {
        $btn.closest('tr').clone(true).insertAfter($btn);
        $btn.closest('table').data('theCount', currentCount + 1);
    }
});

http://api.jquery.com/data/

Getting all rows with common value in column A and specific value in column B

I would recommend three exists clauses -- because you want the original rows:

select mt.*
from matcher_table mt
where exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type1'
             ) and
      exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type2'
             ) and
      exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type3'
             );

The advantage of this approach is that it avoids aggregation and it can make use of an index on matcher_table(matcher, type). I would expect this to have very good performance in comparison to other approaches.

---

Related Topics