CODEWITHSUNDEEP
sqlasp.netdatabase
Karup
Karup

Reputation: 2079

Nested SQL query having select subquery in update statement

I get an error near WHERE clause on executing this query.

update AssetData set EmployeeName = ISNULL(EmployeeName,'') +@reasEmp 
where ([AssetNumber] like'%" + WA_number.Text + "%') 
and ID IN (SELECT ID FROM AssetData ORDER BY ID DESC
where ([AssetNumber] like'%" + WA_number.Text + "%') LIMIT 1)

Someone please help me to figure out what is wrong with this?

Upvotes: 2

Views: 231

Answers (4)

SanyTiger
SanyTiger

Reputation: 666

Try This if you want to select the latest added row according to ID

UPDATE AssetData set EmployeeName = ISNULL(EmployeeName,'') +@reasEmp 
WHERE([AssetNumber] like'%" + WA_number.Text + "%') 
AND ID IN (SELECT MAX(ID) FROM AssetData
where ([AssetNumber] like'%" + WA_number.Text + "%') LIMIT 1)

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

and ID IN (SELECT ID FROM AssetData ORDER BY ID DESC
where ([AssetNumber] like'%" + WA_number.Text + "%') LIMIT 1)

The where should become before ORDER BY. Although legal, in ( ... limit 1) doesn't make sense because in should be used with a list. I recommend using = max(ID) instead

and ID = (SELECT max(ID) FROM AssetData where [AssetNumber] like'%" + WA_number.Text + "%')

you could leave out the first part of your where clause since the ID matches the same criteria already

update AssetData set EmployeeName = ISNULL(EmployeeName,'') +@reasEmp 
where ID = (SELECT max(ID) FROM AssetData where [AssetNumber] like'%" + WA_number.Text + "%');

Upvotes: 3

Dgan
Dgan

Reputation: 10295

try this

i think your are using SQL SERVER

update AssetData set EmployeeName = ISNULL(EmployeeName,'') +@reasEmp 
where [AssetNumber] like'%" + WA_number.Text + "%'
and ID IN (SELECT  TOP 1 ID FROM AssetData
where [AssetNumber] like'%" + WA_number.Text + "%'
ORDER BY ID DESC)

Upvotes: 2

Hart CO
Hart CO

Reputation: 34784

You had an ORDER BY prior to a WHERE. Additionally, based on error message it's clear you're using SQL Server, so you need TOP instead of LIMIT:

update AssetData 
set EmployeeName = ISNULL(EmployeeName,'') +@reasEmp 
where ([AssetNumber] like'%" + WA_number.Text + "%') 
        and ID IN (SELECT TOP 1 ID 
                    FROM AssetData 
                    WHERE ([AssetNumber] like'%" + WA_number.Text + "%') 
                    ORDER BY ID DESC
                    )

Likewise you could choose MAX():

update AssetData 
set EmployeeName = ISNULL(EmployeeName,'') +@reasEmp 
where ([AssetNumber] like'%" + WA_number.Text + "%') 
        and ID IN (SELECT MAX(ID)
                    FROM AssetData 
                    WHERE ([AssetNumber] like'%" + WA_number.Text + "%') 
                    )

Upvotes: 1

Related Questions