Query Syntax Question

Results 1 to 2 of 2

Thread: Query Syntax Question

  1. #1
    Join Date
    Dec 1969

    Default Query Syntax Question

    I&#039;m kind of a n00b to queries, and have a question about how best to approach one I&#039;m trying to whip up.<BR><BR>I have 2 tables that have identical data. One table, Table X, has data populated in it, with each record containing a primary key.<BR><BR>Table Y contains almost the same data (same fields for the most part), except it does NOT have primary keys assigned to it.<BR><BR>What I would like to do is<BR>- First, insert the primary key from the table that DOES have one into the one that does NOT (Im having a hard time with this)<BR><BR>- Then, insert the newly changed records into a 3rd table, where that key is the foreign key (this part is easy)<BR><BR>This is what I have so far:<BR><BR>UPDATE TEMPTABLE <BR>SET ACCT_ID = (<BR>SELECT XP.ACCT_ID FROM<BR>(SELECT DISTINCT ACCOUNTS.ACCT_ID, ACCOUNTS.STORE_NAME FROM ACCOUNTS <BR>INNER JOIN TEMPTABLE ON ACCOUNTS.STORE_NAME = TEMPTABLE.STORE_NAME<BR>WHERE TEMPTABLE.ACCT_ID IS NULL)XP<BR>)<BR><BR>but I get an error back indicating the query returned more than 1 row, so it cant do an update.<BR><BR>TEMPTABLE and ACCOUNTS have a common field - STORE_NAME.<BR><BR>Any idears?<BR>Thanks<BR>TBB

  2. #2
    Join Date
    Dec 1969

    Default *Not* syntax...

    If you had a syntax problem, the query wouldn&#039;t even *try* to execute. It&#039;s a semantic problem.<BR><BR>So let&#039;s analyze: Why do you get more than one row?<BR><BR>Seems like a simple answer: Because your SELECT DISTINCT is returning multiple rows, no? <BR><BR>Consider:<BR><BR>SELECT XP.ACCT_ID FROM (.....) AS XP<BR><BR>will return exactly as many rows as whatever is inside the (...), no?<BR><BR>So... How come you get multiple rows from the (...) query?<BR><BR>That is, from <BR><BR>SELECT DISTINCT ACCOUNTS.ACCT_ID, ACCOUNTS.STORE_NAME FROM ACCOUNTS<BR>INNER JOIN TEMPTABLE ON ACCOUNTS.STORE_NAME = TEMPTABLE.STORE_NAME<BR>WHERE TEMPTABLE.ACCT_ID IS NULL<BR><BR>So why would you get dupes from that? The only way I can see would be if one Accounts.Store_Name had multiple values for Accounts.Acct_ID?<BR><BR>No answers here. Just thinking "out loud" in hopes maybe it will jar something in your own mind.<BR><BR>Why do you bother with the IS NULL clause? Does it really matter if you update an ACCT_ID in TEMPTABLE that already has an ACCT_ID?<BR><BR>Maybe simply try<BR><BR>UPDATE TEMPTABLE <BR>SET ACCT_ID = (<BR>&nbsp; &nbsp; SELECT DISTINCT ACCOUNTS.ACCT_ID<BR>&nbsp; &nbsp; FROM ACCOUNTS, TEMPTABLE<BR>&nbsp; &nbsp; WHERE ACCOUNTS.STORE_NAME = TEMPTABLE.STORE_NAME )<BR><BR>If you get an error from that indicating the internal query returned more than one row, then you *know* the problem is multiple ACCT_ID&#039;s per STORE_NAME. Meaning you&#039;ve got some data to clean up.<BR><BR>You could find the multiples this way, I *think*:<BR><BR>SELECT A1.ACCT_ID AS ID1, A2.ACCT_ID AS ID2, A1.STORE_NAME<BR>FROM ACCOUNTS AS A1, ACCOUNTS AS A2<BR>WHERE A1.STORE_NAME = A2.STORE_NAME<BR>AND A1.ACCT_ID &#060;&#062; A2.ACCT_ID<BR>ORDER BY A1.STORE_NAME<BR><BR>Make sense?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts