CODEWITHSUNDEEP
sqlpostgresql
TheNormalPerson
TheNormalPerson

Reputation: 591

How do i insert a different column from a row?

I have a two tables that look like this: enter image description here

Now to connect two client i need their id, but i'm getting their names instead.

Is there a way of doing it without running a query for each id, and than a final third query to create the connection,

or a better way than doing this: 

INSERT INTO client_conn (c1, c2) VALUES (
(SELECT id FROM clients WHERE name='name1'),
(SELECT id FROM clients WHERE name='name2')
)

If there is something i'm missing than please let me know.

Upvotes: 0

Views: 51

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

One way or the other, if what you have is the names you're going to have to query the table to get the associated IDs. I suggest you use a self-join:

INSERT INTO client_conn (c1, c2)
  SELECT c1.id, c2.id
    FROM clients c1
    INNER JOIN clients c2
      ON c2.name = 'Barney'
    WHERE c1.name = 'Fred';

If both names exist you'll insert the associated ID's into client_conn. If either or both don't exist nothing will be inserted.

dbfiddle here

EDIT

If by chance you decide that you do want NULL values added if the matching names don't exist in clients there's a couple things your can do:

If you're OK with inserting a row if both names are missing you can use

INSERT INTO client_conn (c1, c2)
  SELECT c1.id, c2.id
    FROM (SELECT 1) x
    LEFT OUTER JOIN clients c1
      ON c1.name = 'Betty'
    LEFT OUTER JOIN clients c2
      ON c2.name = 'Wilma'

This will always insert a row, even if neither of the names match a 'clients' row. If you want to insist that at least one of the names match you can add a WHERE clause, as in:

INSERT INTO client_conn (c1, c2)
  SELECT c1.id, c2.id
    FROM (SELECT 1) x
    LEFT OUTER JOIN clients c1
      ON c1.name = 'Pebbles'
    LEFT OUTER JOIN clients c2
      ON c2.name = 'Bam-Bam'
    WHERE c1.name IS NOT NULL OR
          c2.name IS NOT NULL

The above will insert nothing because neither Pebbles or Bam-Bam exist in the clients table, but

INSERT INTO client_conn (c1, c2)
  SELECT c1.id, c2.id
    FROM (SELECT 1) x
    LEFT OUTER JOIN clients c1
      ON c1.name = 'Fred'
    LEFT OUTER JOIN clients c2
      ON c2.name = 'Bam-Bam'
    WHERE c1.name IS NOT NULL OR
          c2.name IS NOT NULL

will insert a row.

dbfiddle here

Upvotes: 1

Miles Elam
Miles Elam

Reputation: 1778

I think Bob Jarvis has the best answer for the on-off immediate case. For readability/maintainability and possibly a speed boost if this ever got sufficiently large is to make a dedicated function for this.

CREATE FUNCTION client_id(p_name varchar) RETURNS integer AS $$
  SELECT id FROM clients AS c WHERE c.name = p_name;
$$ LANGUAGE sql STRICT STABLE LEAKPROOF PARALLEL SAFE;

This makes the insert nicer to look at and more understandable at a glance.

INSERT INTO client_conn (c1, c2) VALUES
(client_id('name1'), client_id('name2'));

The speed boost would come into play if you were inserting several items that shared the same ids.

INSERT INTO client_conn (c1, c2) VALUES
(client_id('name1'), client_id('name2')),
(client_id('name1'), client_id('name3')),
(client_id('name1'), client_id('name4'));

Because the function is marked STABLE and only calls SELECT, PostgreSQL can optimize the call away after the first time. So instead of six separate SELECTs, PostgreSQL can get away with just four since it knows 'name1' will always return the same id each time during the transaction.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You can't do much instead of two queries, you can do one using cross join and an insert select 

  insert client_conn (c1, c2) 
  select  a.id, b.id 
  from  clients a 
  cross join clients b
  where a.name  = 'name1'
    and b.name  ='name2'

Upvotes: 1

Related Questions