update statement - help

[rmck07]

I'm not sure if this is possible or not - I might be hoping for magic

I have a database that receives new records every morning. I'd like to update a companion table with default information about these new records.

The database tracks legislation - every morning, after the new bills have been added to the database, I want to update a table that tracks legislator support for a particular bill (Leg_Decision). The Leg_Decision table needs one record for every legislator for every bill. (There are 90 legislators; there should be 90 records for every bill) The table should be updated with the default value for "undecided" about supporting a bill.

Right now, the task is scripted with ColdFusion, but it's slow and I'm thinking (hoping) if there's a way to do it with straight SQL it might be faster.

Currently, I start by getting all the new bill versions (queryname- getNewKeys):
SELECT Comp_Key
FROM Measures
WHERE NOT EXISTS
(SELECT *
FROM Leg_Decision
WHERE Measures.Comp_Key = Leg_Decision.comp_key)

(is this the right query? I have a nagging feeling this query is problematic)

Then I loop through the list of comp_keys, with a nested loop of the distinct legislator IDs and insert the 90 records for each bill via:
INSERT INTO Leg_Decision (legID, comp_key, decisionID)
VALUES (#legID#, '#getNewKeys.comp_key#', 3)

There has got to be a better way. Right? Is there?

Thanks in advance.

 

[r937]

your getNewKeys query might be better written as a LEFT OUTER JOIN with a NULL test --

SELECT Comp_Key
  FROM Measures
LEFT OUTER
  JOIN Leg_Decision
    ON Leg_Decision.comp_key = Measures.Comp_Key
 WHERE Leg_Decision.comp_key IS NULL

but to answer your question, yes, you should definitely consider changing your "query in a loop" (always a poor performer) to a single INSERT/SELECT query like this --

INSERT 
  INTO Leg_Decision 
     ( legID
     , comp_key
     , decisionID )
SELECT #legID#
     , Comp_Key
     , 3
  FROM Measures
LEFT OUTER
  JOIN Leg_Decision
    ON Leg_Decision.comp_key = Measures.Comp_Key
 WHERE Leg_Decision.comp_key IS NULL

r937.com | rudy.ca

 

[PHV]

Rudy, I think you have an ambiguous column name:
, [!]Measures.[/!]Comp_Key

 

[r937]

yup

less haste, more speed

r937.com | rudy.ca

 

[rmck07]

Thanks! These responses were really helpful. The queries are almost working...

The problem is that every legislator needs a record for every bill. This query runs once for the first legID in the list, then stops because there aren't any null comp_key values anymore.

I need to somehow test for the missing combination of legID and comp_key, instead of just looking for a missing comp_key.

Does that make sense? Any thoughts on how to make this work?

Thank you! I really appreciate it.

 

[PHV]

Do you have a legislators table you may crossjoin ?
INSERT INTO Leg_Decision (legID, comp_key, decisionID)
SELECT L.legID, M.Comp_Key, 3
FROM Legislators L, Measures M
LEFT JOIN Leg_Decision D ON M.comp_key = D.Comp_Key
WHERE D.comp_key IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmck07]

Perfect! Thank you so much!