Click here to Skip to main content
15,313,596 members
Please Sign up or sign in to vote.
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
Updated 18-Jan-18 22:25pm
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

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.
   
Comments
h00h00l00 21-Jan-18 18:45pm
   
Thank you for answer.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900