Toytoy Googie's Geeky blog

I just figured out this trick in MySQL with the INSERT ... SELECT statement functionality. How to do it, so I have a table named OFFERS and table named ACCOUNTS. Let's assumed that I have fields on these table, and it has OFFERS.CODE, OFFERS.USERID and ACCOUNTS.USERID. What I want is, I want to check if ACCOUNTS.USERID exist in OFFERS table in just one SQL statement. To do this, just do      INSERT INTO OFFERS (USERID, CODE)     SELECT         ' 22 ',' XXHH22BBMM '     FROM         ACCOUNT a     WHERE     A.USERID=' 22 ' AND A.USERID  NOT IN (     SELECT OFFER.USERID FROM  OFFER     WHERE OFFER.USERID=' 22 ' )     LIMIT 1 Now, the 22 is the USERID and the XXHH22BBMM values are used as my return value that I wanted actually to be inserted into the database. What exactly is happening here is that, this is equivalent to, INS...

Just simple, declare a variable with that type, mostly number, then   use the 'returning' syntax   for that. set serveroutput on declare  nr_id number; begin  insert into employees( id, name, age )  values( seq_employees.NEXTVAL, 'Jones', 33 )  returning id into nr_id;  dbms_output.put_line( 'nr_id = ' || nr_id ); end; / Hope this helps.  - Toytoy Gogie

So for example, I have my table below to create CREATE TABLE "PC2"."TMP_PRODUCT" (   "ID"              NUMBER(38,0) NOT NULL ENABLE,   "ACCOUNTCODE"     VARCHAR2(20 BYTE),   "PRICESEGMENT"    VARCHAR2(20 BYTE),   "BRAND"           VARCHAR2(20 BYTE),   "PRODUCTLENGTH"   NUMBER,   "SALESID"  NUMBER(38,0),   CONSTRAINT "PK_TMP2_WPP_1009_ID" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255,   CONSTRAINT "FK_TMP2_WPP_1009_SID" FOREIGN KEY ("SALESID") REFERENCES "PC2"."SALES" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE ); Then create a sequence which would manage the incremental part of the index key. CREATE SEQUENCE SEQ_TMP_PRODUCT   START WITH 1 INCREMENT BY 1; Afterwards, create a trigger which what will do is to insert the incremented key of the table. CREATE OR REPLACE TR...