March 15, 2007 at 8:44 am
I'm trying to execute a certain query for only one column, it works fine. But when i try to execute with three column it shows exception. Could anyone provide my the alternate solutions for this query.....
the query is :
INSERT
INTO PHONE_NUMBER_LIST (PHONE_NUMBER,PHONE_MODEL,LANG_PREF)
SELECT
PHONE_NUMBER,MIN(PHONE_MODEL),MIN(LANG_PREF) FROM TEMP_TABLE AS s GROUP BY PHONE_NUMBER
WHERE
NOT EXISTS (SELECT * FROM PHONE_NUMBER_LIST As t WHERE t.PHONE_NUMBER = s.PHONE_NUMBER)
--------------
it's working fine when i'd only one column, i want to try it with my table(consisting of three columns)......
Please help me in this regard......
in advance millions of thank.......
March 15, 2007 at 8:52 am
what kind of error? and i don't see where you are inserting the data into a temp_table anywhere
March 15, 2007 at 9:04 am
my TEMP_TABLE consisting of values with three columns(ph_no,ph_mod,lang_pref). First i'll get the distinct ph_no with their respective ph_mod and lang_pref then i want to check in the PHONE_NUMBER_LIST for the existing of ph_no(in TEMP_TABLE), if it is same then neglect otherwise insert the row. So, in this way my PHONE_NUMBER_LIST will always be consisting of unique ph_no (irrespective of other two columns). So according to that i wrote a query but i'm getting exception as:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
----------------------------
Please help me out.......
March 15, 2007 at 9:27 am
The 'group by' clause should go after the 'where' clause.
INSERT INTO PHONE_NUMBER_LIST (PHONE_NUMBER,PHONE_MODEL,LANG_PREF)
SELECT
PHONE_NUMBER,MIN(PHONE_MODEL),MIN(LANG_PREF) FROM TEMP_TABLE AS s
WHERE
NOT EXISTS (SELECT * FROM PHONE_NUMBER_LIST As t WHERE t.PHONE_NUMBER = s.PHONE_NUMBER) GROUP BY PHONE_NUMBER
March 15, 2007 at 9:30 am
David,
I agree with you, this is what I came up with:
INSERT INTO PHONE_NUMBER_LIST (
PHONE_NUMBER,
PHONE_MODEL,
LANG_PREF
)
SELECT
PHONE_NUMBER,
MIN(PHONE_MODEL),
MIN(LANG_PREF)
FROM
TEMP_TABLE AS s
WHERE
NOT EXISTS (SELECT
*
FROM
PHONE_NUMBER_LIST As t
WHERE
t.PHONE_NUMBER = s.PHONE_NUMBER)
GROUP BY
PHONE_NUMBER
March 15, 2007 at 9:33 am
Here is another alternative solution as well:
INSERT INTO PHONE_NUMBER_LIST (
PHONE_NUMBER,
PHONE_MODEL,
LANG_PREF
)
SELECT
PHONE_NUMBER,
MIN(PHONE_MODEL),
MIN(LANG_PREF)
FROM
TEMP_TABLE AS s
LEFT OUTER JOIN PHONE_NUMBER_LIST AS t
on (s.PHONE_NUMBER = t.PHONE_NUMBER)
WHERE
t.PHONE_NUMBER IS NULL
GROUP BY
PHONE_NUMBER
March 15, 2007 at 9:57 am
thanks............
it worked............
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply