CODEWITHSUNDEEP
sqlt-sql
Gestef
Gestef

Reputation: 128

Update certain amount of records

I need to update a value of half of my records. I am intending to update the records with the query below but I keep getting an error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Would there be a better way of updating the value for half of my results ? Any guidance would be appreciated. 

begin transaction 
update ShoppingCart
SET EpayOrder = '1789614'
WHERE EpayOrder 
in (
select top 188 * from ShoppingCart s
inner join Animal a on a.AnimalPK = s.AnimalFK
inner join Users u on s.UserFK = u.UserPK
where EpayOrder = '1789614' --and s.Deleted is null


)
commit
rollback

Half of the results should have the value as 1789614A under the Epayorder column as to the other half remained the same 1789614.

Upvotes: 0

Views: 47

Answers (2)

SovietFrontier
SovietFrontier

Reputation: 2217

Instead of using * in your sub query, choose the column you need.

change this

SELECT TOP 188 * FROM ShoppingCart s

to this

SELECT TOP 188 EPayOrder FROM ShoppingCart s

Upvotes: 0

dfundako
dfundako

Reputation: 8314

Change your * to a specific column. However, your query as it sits is not updating anything to '1789614A' like you mentioned.

begin transaction 
update ShoppingCart
SET EpayOrder = '1789614'
WHERE EpayOrder 
in (
select top 188 EpayOrder from ShoppingCart s
inner join Animal a on a.AnimalPK = s.AnimalFK
inner join Users u on s.UserFK = u.UserPK
where EpayOrder = '1789614' --and s.Deleted is null


)
commit
rollback

Upvotes: 1

Related Questions