SQL question

Discussion in 'OT Technology' started by Joe_Cool, May 7, 2005.

  1. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,200
    Likes Received:
    510
    I have a table called images and one called users. I need to get the output of a select statement to pull info out of users and use it as the input to an insert statement.

    Can anybody point me in the right direction here? It shouldn't make any difference, but I'm using PostgreSQL.

    Basically I want to do something like this:

    insert into images (col1,col2) values (val1,val2);

    but val1 and val2 are output from this:

    SELECT path||'/'||filename||'.jpg' as FullPath, '/~'||username||filename||'.jpg' as WebPath FROM images,users where images.user_id=users.user_id;
     
  2. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    what language are you using? Have two recordsets, call the first query, then use it's recordset as input for your second query.
     
  3. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,200
    Likes Received:
    510
    I'm doing it directly in the database in SQL, not interfacing through another language.

    And I wrote it wrong: I need to do an UPDATE from a select statement, not an insert. My select gives me a value concatenated from 3 or 4 different values in several tables. I want to update every record in the images table with the concatenated value.
     
  4. Joe_Cool

    Joe_Cool Never trust a woman or a government. Moderator

    Joined:
    Jun 30, 2003
    Messages:
    299,200
    Likes Received:
    510
    Nevermind, I found my answer. Here's an example from http://www.techonthenet.com/sql/update.htm :


    UPDATE supplier
    SET supplier_name = ( SELECT customer.name
    FROM customers
    WHERE customers.customer_id = supplier.supplier_id)
    WHERE EXISTS
    ( SELECT customer.name
    FROM customers
    WHERE customers.customer_id = supplier.supplier_id);
     

Share This Page