Need help with a SQL query

July 7, 2008 4:04 PM

I've got what should be an uber-simple SQL question....

I have a database with ~140,000 entries. I added a column (referred to here as "daviddata"). Currently, all ~140K entries into said column are null, as they should be. However, I want to change all ~140K entries to have some value in that column. Either a '1' or a 'Yes' or something similar. It's a nvarchar(50), if it matters.

How can I quickly update this column? All of the entries in that column will be exactly the same, with no variation, but I can't seem to figure this (seemingly) simple task out. Thanks in advance!

posted by omnipotentq to computers & internet (8 comments total)

I believe this will work:

UPDATE tablename SET davidname = 1 WHERE davidname = *

W3Schools is a good basic reference for this kind of thing
posted by chrisamiller at 4:12 PM on July 7

update table set daviddate = '1'
posted by smackfu at 4:12 PM on July 7

smackfu has it. You don't need a where clause since you want to update every row.
posted by cdmwebs at 4:25 PM on July 7

To both of you, thank you so much! That was perfect.

chrisamiller: I could swear that I looked at every page on the W3Schools SQL site, but apparently I missed that page. Oh well.
posted by omnipotentq at 4:25 PM on July 7

Don't forget to commit your changes afterwards
posted by Calloused_Foot at 4:37 PM on July 7

chrisamiller writes "WHERE davidname = *"

Huh? I have never ever seen this used as a universal predicate (likely because it's unecessary). But I am intrigued! Where does this idiom come from? Where did you see it first, and who uses it?

(In generated code, the idiom "where 1 = 1" can be useful, but "where column = *"? Who uses that?)
posted by orthogonality at 4:38 PM on July 7

Where does this idiom come from? Where did you see it first, and who uses it?

It's from my self-taught and half-remembered rudimentary SQL skills. As noted above, smackfu's answer makes more sense.
posted by chrisamiller at 6:40 PM on July 7

Nobody uses that—it's not proper SQL! ;-)
posted by Khalad at 7:40 PM on July 7