-1

Before I posted this I read this too. But I thought may be my issue was slightly different.

I have a scenario where my table struct is as follows:

my_table:

my_id | what_I_have | what_I_can_do

I don't have a primary key because the table is a lookup table (nothing fundamental). All I want is to get a set of values for my_id, which is based on certain values for what_I_have, and what_I_can_do. Once I get a resultset (with my_d), I want to insert new records into my_table for each of the my_id in the result set with additional information for other columns.

E.g.

01001 | boxes | delivery  | (existing)
01002 | boxes | delivery  | (existing)
01001 | boxes | unpacking | (new)
01002 | boxes | unpacking | (new)

I have thought about doing the following:

INSERT INTO my_table(my_id, what_I_have, what_I_can_do, my_customer_company)
    SELECT 
        my_id, 'boxes', 'unpacking' 
    FROM 
        my_table (NOLOCK)
    WHERE 
        my_id IN (SELECT b.my_id FROM my_table b(NOLOCK) 
                  WHERE b.what_I_have = 'boxes' 
                    AND b.what_I_can_do = 'delivery')

I know that there might be a way to use INNER JOIN and get this done smoothly without "nesting". But is this going to work or am I missing something here?

KR,

Community
  • 1
  • 1
ha9u63a7
  • 6,233
  • 16
  • 73
  • 108
  • 1
    "I don't have a primary key because the table is a lookup table" - that logic is flawed. – Mitch Wheat Sep 21 '15 at 00:09
  • Not sure why the join is required? – JBond Sep 21 '15 at 00:09
  • just put your sub query where clause in the other query where clause – sagi Sep 21 '15 at 00:11
  • Set [Bad Habits to kick - putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is ***NOT RECOMMENDED*** to use this everywhere - quite the contrary! – marc_s Sep 21 '15 at 04:16
  • ***Every*** table ought to have a **primary key** - how else are you going to identify each individual row? – marc_s Sep 21 '15 at 04:17
  • @MitchWheat what I meant was that the my_id is a PK for a different table 'my_items'. I perform association of data on 'my_table' based on PKs from other table, so they are technically all Foreign keys with no PKs for 'my_table'. It's not about flaw.....thanks for your contribution. – ha9u63a7 Sep 21 '15 at 09:13
  • @marc_s no every table doesn't have to have a PK...it's very uncommon __not__ to have one, but it's not impossible....it all depends on your purpose..... – ha9u63a7 Sep 21 '15 at 09:14

1 Answers1

2

I don't think you need nesting or a join at all:

INSERT INTO my_table(my_id, what_I_have, what_I_can_do, my_customer_company)
    SELECT my_id, 'boxes', 'unpacking'
    FROM my_table
    WHERE what_I_have = 'boxes' AND what_I_can_do = 'delivery';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786