在Firebird中插入选择 [英] INSERT SELECT in Firebird

问题描述

我是Firebird的新手，我遇到了很多问题.我想在从另一个表中选择的表中插入各种行.

I'm new to firebird and I have verious issues. I want to insert various lines into a table selected from another table.

代码如下:

/*CREATE GENERATOR POS; */
SET GENERATOR POS TO 1;

SET TERM ^;

create trigger BAS_pkassign
   for MATERIAL
active before insert position 66

EXECUTE BLOCK
AS

  declare posid bigint;
  select gen_id(POS, 1)
  from RDB$DATABASE
  into :posid;

BEGIN



END

SET TERM ; ^


INSERT INTO MATERIAL ( /*ID */ LOCATION, POSID, ARTID, ARTIDCONT, QUANTITY )
SELECT  1000, ':posid', 309, BAS_ART.ID, 1
FROM    BAS_ART
WHERE   BAS_ART.ARTCATEGORY LIKE '%MyWord%'

ID应该从66开始自动递增. posid应该从1开始自动递增.

The ID should autoincrement from 66 on. The posid should autoincrement from 1 on.

实际上，它没有插入任何内容.

Actually it is not inserting anything.

我正在使用Firebird Maestro，并且刚刚打开了SQL脚本编辑器(在执行脚本时不会抛出任何错误消息).

I'm using Firebird Maestro and have just opened the SQL Script Editor (which doesnt throw any error message on executing the script).

有人可以帮助我吗?

谢谢！

其他信息:

触发器应自动增加"ID"列-但我不知道如何精确地更改它才能起作用..:: posid使用它:posid会引发错误，但这样就没有错误(我猜它是解释为字符串).但是我如何正确使用它呢?

The trigger should autoincrement the column "ID" - but I dont know how exactly I can change it so it works.. The ':posid' throws an error using it :posid but like this theres no error (I guess its interpretated as a string). But how do I use it right?

执行它时我没有收到错误.表格结构简单.我有2张桌子: 1.

I dont get errors when I execute it. The table structure is easy. I have 2 tables: 1.

 Material (
ID (INTEGER),
Location (INTEGER),
POSID (INTEGER),
ARTID (INTEGER),
ARTIDCONT (INTEGER),
QUANTITY (INTEGER),
OTHERCOLUMN (INTEGER)) 

和其他2.表

BAS_ART (ID (INTEGER), ARTCATEGORY (VARCHAR255))

->我想将表BAS_ART中所有包含ARTCATEGORY列中"MyWord"的条目插入到MATERIAL表中.

-> I want to insert all entries from the table BAS_ART which contain "MyWord" in the column ARTCATEGORY into the MATERIAL table.

推荐答案

我根本不明白为什么需要触发器.

I don't understand why you need the trigger at all.

此问题:

我想将BAS_ART表中所有包含"MyWord"的条目插入到MATERIAL表中

可以用一个insert ... select语句解决.

Can be solved with a single insert ... select statement.

insert into material (id, location, posid, artid, quantity)
select next value for seq_mat_id, 1000, next value for seq_pos, id, 1
from bas_art
where artcategory = 'My Word';

这假定存在名为seq_mat_id的第二个序列(也称为生成器")，该序列为列material.id

This assumes that there is a second sequence (aka "generator") that is named seq_mat_id that provides the new id for the column material.id

这篇关于在Firebird中插入选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！