Update random row syntax problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update random row syntax problem

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-11 : 14:20:52

I want to update a row in a random row. I am using this statement.

UPDATE YSeed_UniqueSeedNames
SET OrderNumber = 'test' where (SELECT TOP 1 * FROM YSeed_UniqueSeedNames where OrderNumber = ''
ORDER BY NEWID())

But i get this error...

Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

I have not been able to find the syntax error. What am i doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 14:25:33

WHERE what value?

With SQL Server you can use the new UPDATE TOP syntax.

UPDATE TOP (1) YSeed_UniqueSeedNames
SET OrderNumber = 'test'
WHERE OrderNumber = ''
ORDER BY NEWID()

E 12°55'05.25"
N 56°04'39.16"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-11 : 16:38:15

I want to populate OrderNumber where OrderNumber is empty. I tried the following statement anf got this error...

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ORDER'.

Executed code..

UPDATE TOP (1) YSeed_UniqueSeedNames
SET OrderNumber = 'test'
WHERE OrderNumber = ''
ORDER BY NEWID()

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 16:45:33

My mistake. Remove the ORDER BY line.

UPDATE TOP (1) YSeed_UniqueSeedNames
SET OrderNumber = 'test' 
WHERE OrderNumber = ''

E 12°55'05.25"
N 56°04'39.16"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-11 : 17:36:46

In my original attempt I used...

SELECT TOP 1 * FROM YSeed_UniqueSeedNames where OrderNumber = ''
ORDER BY NEWID()

Because i need to update a random row where the OrderNumber is empty. The suggested statement picks rows in order.

I am attempting to get the random select of a row to work in the context of an update.

UPDATE YSeed_UniqueSeedNames
SET OrderNumber = 'test' where (SELECT TOP 1 * FROM YSeed_UniqueSeedNames where OrderNumber = ''
ORDER BY NEWID())

I was thinking there is a syntax error. but can what i want to do be done?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:41:14

The only way to make your approach work is if you have a primary key on the table.

E 12°55'05.25"
N 56°04'39.16"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-11 : 17:47:29

There is a PK in the file. How would i use it to update a random row?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:01:04

[code]UPDATE t
SET t.OrderNumber = 'test'
FROM YSeed_UniqueSeedNames AS t
WHERE t.PkCol = (SELECT TOP 1 m.PkCol FROM YSeed_UniqueSeedNames AS m WHERE m.OrderNumber = '' ORDER BY NEWID())[/code]

E 12°55'05.25"
N 56°04'39.16"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-11 : 18:20:08

That's is it! I'll have to study that solution to understand how it works.

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources