help with query

hi guys
why this query do not work

INSERT INTO suplementos(numdeord)SELECT DISTINCT numdeord FROM odc LEFT JOIN suplementos ON odc.numdeord=suplementos.numdeord WHERE suplementos.numdeord IS NULL

error
ambigous column name

tsm
ErnestoAsked:
Who is Participating?
 
Om PrakashConnect With a Mentor Commented:
try:

INSERT INTO
suplementos (numdeord)
SELECT
      DISTINCT
            odc.numdeord
      FROM odc
      LEFT JOIN suplementos
      ON odc.numdeord=suplementos.numdeord WHERE suplementos.numdeord IS NULL
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>ambigous column name
That's because numderord exists in both odc and suplementos tables, and T-SQL doesn't know which one you want to insert, and won't assume for you.

INSERT INTO suplementos(numdeord)SELECT DISTINCT odc.numdeord FROM odc LEFT JOIN suplementos ON odc.numdeord=suplementos.numdeord WHERE suplementos.numdeord IS NULL

or

INSERT INTO suplementos(numdeord)SELECT DISTINCT suplementos.numdeord FROM odc LEFT JOIN suplementos ON odc.numdeord=suplementos.numdeord WHERE suplementos.numdeord IS NULL
0
 
Simone BSenior E-Commerce AnalystCommented:
You need to specify where you're getting numdeord from:

INSERT INTO suplementos(numdeord)
SELECT DISTINCT odc.numdeord
FROM odc
LEFT JOIN suplementos ON odc.numdeord=suplementos.numdeord WHERE suplementos.numdeord IS NULL
0
 
BAKADYCommented:
Try:

INSERT suplementos ( numdeord )
SELECT odc.numdeord
FROM odc LEFT JOIN suplementos
    ON odc.numdeord = suplementos.numdeord
GROUP BY odc.numdeord
HAVING COUNT( suplementos.numdeord ) = 0

Saludos
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.