Sql subquery (select+update)

Question

0.00/5 (No votes)

See more:

We want to change the age value of the 9th data among the 100 data in the 'customer'
table to 19.

Can I make the following two lines of a query into a single line with a subquery?

select * from customer limit 9, 1

update customer set age = 19

What I have tried:

update customer set age=19 where ???? in (select *from customer limit 9, 1)

Posted 18-Jan-18 15:04pm

h00h00l00

Updated 18-Jan-18 22:25pm

Add a Solution

Comments

David_Wimbley 18-Jan-18 23:09pm

update customer set age=19 where ???? in (select *from customer limit 9, 1)

is the general idea but there has to be an ID column or some unique identifier to this table that allows you to specify the table. Maybe its Select Id FROM Customer or Select CustomerId From Customer but that would be how you'd do this query.

Given we don't have access to your server, your DB table, nor can we see the schema...that is about as good as it gets.

1 solution

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)

OriginalGriff · Answer 1 · 2018-01-18T22:25:00

The problem with your query as it stands is that it isn;t guaranteed to return any specific results: unless you specify an ORDER BY clause in your SELECT query, SQL is at liberty to return rows in any order it sees fit - and that means that two successive SELECTs may not return the same results.
This is why tables normally have an ID column of some form (often an INT IDENTITY or a UNIQUEIDENTIFIER field) so that you can absolutely specify which row you are interested in.
In your case, you are trying to set the age of a specific person, and assuming that because they are currently the ninth row in the table they will always be. Even ignoring that you aren't specifying any order, that's not safe: What happens if someone dies (or a customer goes bust) and you remove their row as a result? If it's before the ninth, then your update hits the wrong person. If it's after, you have to check to ensure that it if after!

So don't do it like that: find the ID for the row (or add a column to your table, it's quite common to have multiple customers with the same name, but different customer IDs) and use that in a WHERE clause of your UPDATE command.