Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Aug 2008
    Posts
    1

    Unanswered: INSERT with SELECT problem

    Hi,

    I'm trying to insert a row into a table, whose columns are two foreign keys (which I want to retrieve in this query, with SELECT).
    I'm trying to do something like this:

    Code:
    insert into A (fk_B_id, fk_C_id)  
            values 
                (select B.id from B where              
                  B.name='foo', 
                select C.id from C where               
                  C.name='bar') ;
    However, this doesn't work..
    I know that you can insert a single select into a table, but what about two selects? Is it possible?

    Thank you

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Specify either the values clause OR the select
    Not both i.e.
    insert into t1 select a, b from t2, t3....
    Code:
    Syntax:
    insert [into] [database.[owner.]]{table_name|view_name}
        [(column_list)] 
        {values (expression [, expression]...)
            |select_statement [plan "abstract
    plan"] }

  3. #3
    Join Date
    Jan 2009
    Posts
    4

    Same problem here

    I have the exact same problem but couldn't get it to work.

    Can someone please give some light over this issue?

    Thanks in advance,

    Andre

  4. #4
    Join Date
    Jan 2009
    Posts
    6
    Try the below,

    insert into A (fk_B_id, fk_C_id)
    select
    (select B.id from B where
    B.name='foo')B,
    (select C.id from C where
    C.name='bar')C

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Dunno about sybase, but "union all" is ANSI SQL, right? So try
    Code:
    insert into A (fk_B_id, fk_C_id)  
            values 
                (select B.id from B where    B.name='foo' 
                 union all
                 select C.id from C where   C.name='bar'
                 ) ;

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I know that you can insert a single select into a table, but what about two selects? Is it possible?
    You use "values" if you're inserting a single set of constant values OR you use select to insert data from another table etc. In both cases you have to have the same number of values being selected as you want to insert - you're trying to insert 2 rows of 1 value in the cases above into a table with 2 fields. Try:
    Code:
    insert A (fk_B_id, fk_C_id)  
    select B.id, C.id
    from B , C
    where    B.name='foo' 
               and C.name='bar'

  7. #7
    Join Date
    Jan 2009
    Posts
    4
    Thank you very much! Worked like a charm.
    In case anyone has the same problem my final syntax is:

    insert into trades (idUser,idBook, idTrader, idSecurity, idCounterparty)

    select users.idUser, books.idBook, traders.idTrader, securities.idSecurity, counterparties.idCounterparty

    from users , books, traders, securities, counterparties

    where users.nameUser= 'Bill'
    and books.nameBook='Book1'
    and traders.nameTrader='Joe'
    and securities.nameSecurity='DOL-G09'
    and counterparties.nameCounterparty='Ativa'

  8. #8
    Join Date
    Jan 2009
    Posts
    4

    Wink

    Does anyone knows how to cope with this same problem in a UPDATE query?

    I mean, I want to update table A columns that are table B PK and table C PK.

    I only have the new names of the records from table B and C -that could be used to return me the PKs.

    I tried a lot of different comands but none seemed to work...

    something like:

    UPDATE tableA

    SET tableA.idB = tableB.id, tableA.idC = tableC.id

    WHERE tableB.name = [nameB] AND tableC.name = [nameC]

    Thanks in advance!

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need a from clause
    Syntax:
    Code:
       update [[database.]owner.]{table_name | view_name}                           
          set [[[database.]owner.]{table_name.|view_name.}]                         
              column_name1 =                                                        
                  {expression1|NULL|(select_statement)} |                           
              variable_name1 =                                                      
                  {expression1|NULL|(select_statement)}                             
              [, column_name2 =                                                     
                  {expression2|NULL|(select_statement)}]... |                       
              [, variable_name2 =                                                   
                  {expression2|NULL|(select_statement)}]...                         
                                                                                    
          [from [[database.]owner.]{view_name [readpast]|                           
              table_name [readpast]                                                 
                  [(index {index_name | table_name }                                
                  [ prefetch size ][lru|mru])]}                                     
               [,[[database.]owner.]{view_name [readpast]|                          
              table_name [readpast]                                                 
                  [(index {index_name | table_name }                                
                  [ prefetch size ][lru|mru])]}]                                    
          ...]                                                                      
          [where search_conditions]                                                 
          [plan "abstract plan"]                                                    
          update [[database.]owner.]{table_name | view_name}                        
          set [[[database.]owner.]{table_name.|view_name.}]                         
              column_name1 =                                                        
                 {expression1|NULL|(select_statement)} |                            
              variable_name1 =                                                      
                  {expression1|NULL|(select_statement)}                             
              [, column_name2 =                                                     
                 {expression2|NULL|(select_statement)}]... |                        
              [, variable_name2 =                                                   
                  {expression2|NULL|(select_statement)}]...                         
          where current of cursor_name
    UPDATE tableA
    SET idB = tableB.id, idC = tableC.id
    from tableB, tableC
    WHERE tableB.name = [nameB] AND tableC.name = [nameC]

    PS. The insert solution you posted in post#7 was already sugested to you in post#2

  10. #10
    Join Date
    Jan 2009
    Posts
    4
    Thank you again. Neat solution.

    I also discovered a work around for that.

    I saved auxiliary queries that returned me the name field from the id.

    Then I used those queries in the design view in my update statement. That worked well too, even though it was not a perfect solution, but very easy to implement and maintain.

    Regards,

    André

  11. #11
    Join Date
    Feb 2009
    Location
    Sweden
    Posts
    19
    Hi guys,
    its so interesting this information about the SQL Insert ... am using the same thing but its not working ...

    INSERT INTO Temp2 (test='2342fff', ProduktID, ProduktTypID, FargID, PlatsID, Lasbom)SELECT ProduktID, ProduktTypID, FargID, PlatsID, Lasbom FROM Produktid WHERE (ProduktID = 3)

    am getting error in that !!!

    i'll be very thankful if somebody helped me on that

    thanks

    Abra

  12. #12
    Join Date
    Feb 2009
    Location
    Sweden
    Posts
    19
    also the same for Update :

    UPDATE tem4 SET (ProduktID, ProduktTypID, FargID, PlatsID, Lasbom) = (SELECT ProduktID, ProduktTypID, FargID, PlatsID, Lasbom FROM Produktid WHERE (ProduktID =3)) WHERE seid ='345erty'

    as tem4 has (seid, ProduktID, ProduktTypID, FargID, PlatsID, Lasbom )
    and Produktid has (ProduktID, ProduktTypID, FargID, PlatsID, Lasbom)

    thanks in advance.

    Abra

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Abrahm75
    its so interesting this information about the SQL Insert
    Generally with computer languages you're not allowed to create your own syntax, you have to stick with the official syntax. Your insert statement should be :
    Code:
    INSERT INTO Temp2 (ProduktID, ProduktTypID, FargID, PlatsID, Lasbom)
    SELECT ProduktID, ProduktTypID, FargID, PlatsID, Lasbom 
    FROM   Produktid 
    WHERE  ProduktID = 3
    but Lord only knows what you're trying to do with that update statement - can you put it into words?

  14. #14
    Join Date
    Feb 2009
    Location
    Sweden
    Posts
    19
    thank you for your help actually you are right the Insert Statment is working in that way ...
    ok then there is no way to do the insert in another way then i need to write in many SQL statment INSERT, SELECT & UPDATE, in 3 Steps

    1) INSERT INTO tem5 (Seid, ProduktID) VALUES('346tutu',3)

    then to SELECT from the Produktid to prepear it

    2) SELECT ProduktID, ProduktTypID, FargID, PlatsID, Lasbom FROM Produktid WHERE (ProduktID = 3)

    in this one i put the all feached information i mean (ProduktID, ProduktTypID, FargID, PlatsID, Lasbom ) to (P1, P2, P3, P4, P5) sequencly

    finally UPDATE the same record that we already used INSERT step (1)

    3) UPDATE tem5 SET ProduktTypID=p2,FargID=p3, PlatsID=p4, Lasbom=p6 WHERE (seid = '242jhjkh') AND (ProduktID=p1)

    thats why i was thinking that i can fix the step 1 and 2 or step 2 and step 3
    in one SQL statment. if you now understand what i mean ...

    thank you any way for your help man

    Abra

  15. #15
    Join Date
    Feb 2009
    Location
    Sweden
    Posts
    19
    sorry for my mistake in step (3)

    3) UPDATE tem5 SET ProduktTypID=p2,FargID=p3, PlatsID=p4, Lasbom=p5 WHERE (seid = '242jhjkh') AND (ProduktID=p1)

    *** there is no p6 its p5

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •