Duplicate column name

Question

Post reply

Duplicate column name

Sandeep Mahajan-393729

SSC Eights!

Points: 858
More actions
August 6, 2008 at 4:14 am

#196506

When i run this query :
UPDATE intv_schedule SET program_id = (SELECT e.program_id FROM (SELECT * FROM intv_episode e, intv_schedule s WHERE e.episode_id = s.episode_id )as ex);
I got an error :
Duplicate column name 'episode_id'
Where went wrong...?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Any reason why you don't do something like...

UPDATE intv_schedule

SET program_id = (

SELECT e.program_id

FROM intv_episode e,

intv_schedule s

WHERE e.episode_id = s.episode_id)

By the way, your ';' at the end sounds very Oracleish, isn't it? 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

HHI,

There are a few places you went wrong I'll try and explain.

For simplicity I'll just look at your select statements.

[Code]

SELECT e.program_id

FROM (SELECT *

FROM intv_episode e, intv_schedule s

WHERE e.episode_id = s.episode_id )as ex

[/Code]

Because the subquery in the above statement is returning all rows from all tables which have the same rows you will have a duplicate of episode.

Also because you only need e.program_Id probably best to just return that.

e.g

[Code]

(SELECT e.program_id

FROM intv_episode e, intv_schedule s

WHERE e.episode_id = s.episode_id )as ex

[/code]

Also the main section of your select you are using e.program_ID this should in face be ex.programID.

UPDATE intv_schedule

SET program_id = (SELECT ex.program_id

FROM (SELECT e.program_id

FROM intv_episode e, intv_schedule s

WHERE e.episode_id = s.episode_id)as ex

);

Further to this you could remove the outer select leaving you with something like the previous post by PaulB

hope that helps

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

There is another problem in that how does your query know which related intv_schedule.program_Id gets which ex.Program_ID?

I think this is in fact what you looking for:

UPDATE s

SET program_id = e.program_ID

FROM intv_schedule s

INNER JOIN intv_episode e

ON e.episode_id = s.episode_id

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Sandeep Mahajan-393729 SSC Eights! Points: 858 More actions · Answer 4

Hi Chris,

Thanks, but your query gives one another error :

:Subquery returns more than 1 row

I want to update all program_id according to episode_id in episode table.

Waiting for your reply...

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

does my last reply not work?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Sandeep Mahajan-393729 SSC Eights! Points: 858 More actions · Answer 6

Sandeep Mahajan-393729

SSC Eights!

Points: 858

August 6, 2008 at 5:22 am

#853722

Hi Chris,

Thanks for your help ..Now It's Working!!