Problem for Update

Question

0.00/5 (No votes)

See more:

hi,

I have a table line (ID, LineNo, LineName) having values as :

1, 1, <blank>
2, 2, <blank>
3, 3, <blank>

Now i have to write a query such that it updates LineName column from line table which is blank(as shown above with <blank>) with values from table linet(linenamet) having values as :

a
b
c

My trial query is :
update line set LineName = (select linenamet from linet);

It gives error as : "Subquery returns more than 1 row"

Expected Result:
After Updation it should show line table as:

1, 1, a
2, 2, b
3, 3, c

Kindly let me know the update SQL query for the same.

Thanks and Regards,

Posted 5-Jan-11 2:14am

shwetavc30

Updated 5-Jan-11 2:23am

Manfred Rudolf Bihy

v2

Add a Solution

Comments

Manfred Rudolf Bihy 6-Jan-11 3:25am

Glad I could help you!
You can also choose to accept my answer if it helped you solve your problem. Thank you!

Manfred Rudolf Bihy 6-Jan-11 3:41am

Thanks, I appreciate it!

shwetavc30 6-Jan-11 3:45am

Sorry for that..It just slipped out my mind. Thanks for reminding :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hiren solanki · Accepted Answer · 2011-01-05T02:28:00

Solution 1

Following query what you've written will cause error you stated definately because you are getting more then one linenamet from linet, That is 3 for you example.

update line set LineName = (select linenamet from linet);

For that you can modify your query like.

update line set LineName = (select linenamet from linet --include some condition which gives you exact single result from corrosponding table)

If there's a single column in linenamet table then I think it is not possible to fetch.

Posted 5-Jan-11 2:28am

Hiren solanki

Comments

Manfred Rudolf Bihy 5-Jan-11 8:35am

That is the same conclusion I came to! 5+
There needs to be a link between line and linenamet, either by foreign keys or an extra table that sets up the relation.

Hiren solanki 5-Jan-11 8:37am

Thanks manfred.

JF2015 5-Jan-11 8:43am

Good one!

shwetavc30 5-Jan-11 23:42pm

Hey thanks Hiren. Actually initially there wasn't a key for table linet. But after your solution, I produced the one and it worked great. Thanks a lot for the explanation.

Manfred Rudolf Bihy · Accepted Answer · 2011-01-05T02:33:00

If your table linet also had a primary key to it that would link it (foreign key) to a either ID or LineNo in table line it would look like this
Column named ID in linet is foreign key referencing line column ID:

SQL

UPDATE line SET LineName = (SELECT linenamet FROM linet ln WHERE ln.ID = line.ID)

Column named ID in linet is foreign key referencing table line column LineNo:

SQL

UPDATE line SET LineName = (SELECT linenamet FROM linet ln WHERE ln.LineNo = line.ID)

Since we don't know if table linet has any foreign key or any key at all you need to tell us. Maybe there is a third table that makes the relation between the tables linet and line.

Best Regards,
Manfred