CODEWITHSUNDEEP
sqlpostgresql
WolfieeifloW
WolfieeifloW

Reputation: 619

INSERT INTO SELECT MAX From Another Table

I have an INSERT INTO SELECT statement that right now has a static number. I'd like to make this number dynamic by making it the MAX of a field from another table.

The statement is:

INSERT INTO checklist_items (checklists_id, checklist_item_types_id, is_completed)
    SELECT 3, cit.id, false
    FROM checklist_item_types cit
    WHERE cit.is_active = 't'

Here's a sample of each of the tables:

Here is my checklists table:

id  checklist_date  notes
1   "2018-07-23"    "Fixed extra stuff"
2   "2018-07-24"    "These are some extra notes"
3   "2018-07-25"    "Notes notes"

Here is my checklist_items table, data reduced:

id  checklists_id  checklists_item_types_id  is_completed
1   1              1                         false
2   1              2                         true
3   1              3                         true
...
34  2              16                        true
35  2              17                        true
36  2              18                        true

And here is checklist_item_types, data reduced:

id  description                        is_active
1   "Unlock Entrances"                 true
2   "Ladies Locker Room Lights"        true
3   "Check Hot Tubs (AM)"              true
...
15  "Water Softener Boiler Room"       false
16  "Water Softener Laundry"           true
17  "Check/Stock Fire Logs"            true
18  "Drain Steam Lines (4 locations)"  true

How do I go about changing SELECT 3 to something like SELECT MAX(checklists.id)?

Upvotes: 4

Views: 3475

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

YOu could use an inner join and group by 

  INSERT INTO checklist_items (checklists_id, checklist_item_types_id, is_completed)
      SELECT max(c.id), cit.id, false as status
      FROM checklist_item_types cit
      INNER JOIN checklists c  ON c.id =  cid.checklists_id
      WHERE cit.is_active = 't'
      group by  cit.id, status

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

Simply replace the 3 with a subquery:

INSERT INTO checklist_items (checklists_id, checklist_item_types_id, is_completed)
    SELECT (SELECT MAX(id) FROM checklists), cit.id, false
    FROM checklist_item_types cit
    WHERE cit.is_active = 't'

Upvotes: 7

Related Questions