Запрос на обновление в sqlite

[Karl-Philipp]

Здравствуйте!

В базе данных есть 2 таблицы, созданные таким образом

Код:

CREATE TABLE rootTable(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  idName VARCHAR(10) NOT NULL
);

CREATE TABLE dataTable(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  rootTableId INTEGER(10) NOT NULL,
  rootTableIdName VARCHAR(10)
);

первая таблица заполнена значениями, во второй тоже есть записи, в которых не заполнено поле rootTableIdName.
Подскажите, пожалуйста, как сделать запрос на заполнение поля rootTableIdName в таблице dataTable, если данное поле должно заполняться на основе сравнения полей rootTableId из dataTable
и id из rootTable ()?

[Rcus]

ну наверно самым очевидным способом так:

Код

SQL
UPDATE dataTable SET rootTableIdName = (SELECT idName FROM rootTable WHERE rootTable.id = rootTableId);

[Karl-Philipp]

ну наверно самым очевидным способом так:

Код

SQL
UPDATE dataTable SET rootTableIdName = (SELECT idName FROM rootTable WHERE rootTable.id = rootTableId);

спасибо большое

[Karl-Philipp]

Возникла необходимость обновить данные поля sequenceId в порядке возрастания натуральных чисел (1,2,3...,N).

Код

SQL
CREATE TABLE dataTable(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  flagId INTEGER,
  squenceId INTEGER,
 );

Обновляться должны те данные в sequenceId, у которых в записи присутствует конкретный флаг (flagId)

Решил создать временную таблицу для записи id из dataTable, после чего обновляю dataTable.sequenceId из временно созданной таблицы:

Код

C++ (Qt)
QSqlDatabase db;
int colId; 
...
QSqlQuery query(db);
if (query.exec(QString("CREATE TEMP TABLE tempSequence(id INTEGER PRIMARY KEY AUTOINCREMENT, dataTableId INTEGER NOT NULL)"))) {
   if (query.exec(QString("INSERT INTO tempSequence(dataTableId) SELECT id FROM dataTable WHERE flagId = %1)")
    .arg( QVariant( colId ).toString())   )) {   
      if (query.exec(QString("UPDATE dataTable SET sequenceId = (SELECT id FROM tempSequence WHERE dataTable.id = dataTableId")))
      {
       //dosmth
      }
   }
}

То есть, если данные таблицы dataTable были такими

1|1|
2|1|
3|2|
4|2|
5|2|

после выполнения предложенных выше запросов, данные в таблице dataTable при colId = 2 должны выглядеть так:

1|1|
2|1|
3|2|1
4|2|2
5|2|3

вроде должно работать, но может есть какой-то более оптимальный вариант?    

[Tonal]

Вообще-то никто не обещал, что SELECT внутри INSERT-а будет возвращать записи именно в порядке возрастания id.
Так что самое надёжное - цикл ручками.

[Karl-Philipp]

а если упорядочить?

Код

SQL
INSERT INTO tempSequence(dataTableId) SELECT id FROM dataTable WHERE flagId = %1 ORDER BY id)

------------------
если приведенный вариант не подойдет, направьте, пожалуйста, как с помощью цикла выбирать нужные мне id?

[Tonal]

По идее и INSERT не обязан учитывать какой бы-то ни было порядок в поступающем множестве записей.
Хотя, представить что это не так сложно, но можно (многопоточная вставка с пулами id).

Ну а руками вроде просто - читать в цикле SELECT id FROM dataTable WHERE flagId = %1 и вызывать на каждый
UPDATE dataTable SET sequenceId = %1 WHERE dataTable.id = %2 - где первый параметр - счётчик, а второй id.

[Karl-Philipp]

Tonal, спасибо, наверное так и сделаю.

Однако возник еще один вопрос, связанный с обновлением  данных. В продолжение третьего поста этой темы:
(Возникла необходимость обновить данные поля sequence в порядке возрастания натуральных чисел (1,2,3...,N) для конкретных flagId.

Была задействована временная таблица для записи id из таблицы t c учетом конкретных flagId. После чего обновлялось поле sequence из поля id временной таблицы. Далее удалялись записи временной таблицы и выбирался новый flagId для заполнения временной таблицы:

Код

SQL
sqlite> CREATE TABLE t (id integer PRIMARY KEY, flagId integer, sequence integer);
sqlite> INSERT INTO t (flagId) VALUES (1);
sqlite> INSERT INTO t (flagId) VALUES (1);
sqlite> INSERT INTO t (flagId) VALUES (1);
sqlite> INSERT INTO t (flagId) VALUES (2);
sqlite> INSERT INTO t (flagId) VALUES (2);
sqlite> INSERT INTO t (flagId) VALUES (2);
sqlite> INSERT INTO t (flagId) VALUES (2);
sqlite> SELECT * FROM t;
1|1|
2|1|
3|1|
4|2|
5|2|
6|2|
7|2|
sqlite> CREATE temp TABLE tt(id integer PRIMARY KEY, t_id integer);
sqlite> INSERT INTO tt(t_id) SELECT id FROM t WHERE flagId = '1';
sqlite> SELECT * FROM tt;
1|1
2|2
3|3
sqlite> UPDATE t SET sequence = (SELECT id FROM tt WHERE t.id = t_id ORDER BY id);
sqlite> DELETE FROM tt;
sqlite> SELECT * FROM t;
1|1|1
2|1|2
3|1|3
4|2|
5|2|
6|2|
7|2|
sqlite> SELECT * FROM tt;
sqlite> INSERT INTO tt(t_id) SELECT id FROM t WHERE flagId = '2';
sqlite> SELECT * FROM tt;
1|4
2|5
3|6
4|7
sqlite> UPDATE t SET sequence = (SELECT id FROM tt WHERE t.id = t_id ORDER BY id);
sqlite> SELECT * FROM t;
1|1|
2|1|
3|1|
4|2|1
5|2|2
6|2|3
7|2|4
sqlite>

подскажите, пожалуйста, почему удалились значения t.sequence (1,2,3), которые были вставлены ранее?
Как надо правильно сделать?

[Tonal]

Возможно ты что-то не от с транзакциями.
Такое впечатление, что второй и третий SELECT * FROM t; проходят в разных транзакциях. Причём первая завершилась по ROLLBACK.

Да и поведение временной таблицы странное - если там AUTOINCREMENT (как в твоём 3ем посте) на первичном ключе, то вторая последовательность должна начинаться с id = 4.

П.С. Ты точную запись сессии привёл, или собрал из нескольких и подредактировал?

[Karl-Philipp]

последний вариант - сделал заново. Привёл точную запись сессии из консоли

[Rcus]

UPDATE без условия выборки применяется ко всем записям в таблице, пустая выборка соответствует NULL значению.

[Karl-Philipp]

Rcus, спасибо большое.
заменил

Код

SQL
UPDATE t SET sequence = (SELECT id FROM tt WHERE t.id = t_id ORDER BY id);

на

Код

SQL
UPDATE t SET sequence = (SELECT id FROM tt WHERE t.id = t_id ORDER BY id) WHERE sequence IS NULL;

и заработало