INSERT SELECT from multiple tables

[ratinakage]

Hi there,

I am wanting to run an insert that uses two values form two select statements.

I think it should be clear what I'm trying to do. Basically get the two contact IDs for the first two values (and 4 for the relationship_type_id and 1 for is_active).

INSERT INTO civicrm_relationship 
  (contact_id_a, contact_id_b, relationship_type_id, is_active)
  (SELECT                   AA,
                            BB,
                            4,1
  FROM
    (SELECT id       AA      FROM civicrm_contact WHERE external_identifier = 283), 
    (SELECT id       BB      FROM civicrm_contact WHERE external_identifier = 274))

The error I get is: Every derived table must have its own alias

Any help would be much appreciated!
Thanks...

 

[gmmastros]

If you run this query:

SELECT id AA FROM civicrm_contact WHERE external_identifier = 283

by itself, how many rows do you get? Similarly for the external identifier = 274?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pjw001]

I think what you want is
INSERT INTO civicrm_relationship
(contact_id_a, contact_id_b, relationship_type_id, is_active) (SELECT AA.id, BB.id, 4,1
FROM (SELECT id FROM civicrm_contact WHERE external_identifier = 283) AA, (SELECT id FROM civicrm_contact WHERE external_identifier = 274) BB)

and you might have too many brackets.

 

[ratinakage]

Thanks pjw001!!

You solved it!

Not sure how your brackets work but it works....