Welcome to WebmasterWorld Guest from 54.234.114.182

Forum Moderators: open

Message Too Old, No Replies

SQL Update

Column where Column = Column

     
10:14 pm on Mar 5, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 22, 2003
posts:1022
votes: 0


Greetings to the forum

I seem to be having a problems again with basic SQL :(
Here is what I am trying to do:

UPDATE table
SET column1 = 'value1'
WHERE column2 = 'value1'
AND column1 = '';

I am trying to update column1 with the same value that is in column2 if and only when column1 is empty or NULL.

TIA

-Gs
12:06 am on Mar 6, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5628
votes: 47


Try:
UPDATE table SET
column1 = 'value1'
WHERE column2 = 'value1' AND (column1 = '' OR column1 IS NULL);
10:26 am on Mar 6, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 22, 2003
posts:1022
votes: 0


Thanks so much LifeinAsia

That was the one, I had been playing around with it and never getting the proper command :(

Thanks again

-Gs
9:41 am on Apr 7, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Mar 15, 2010
posts:57
votes: 0


SET is an sql command that will assign a value to the variable. Thanks for sharing, it was a quick brush up for me.
12:47 pm on Apr 7, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 19, 2002
posts:3207
votes: 13


why not

update table
set column1=column2
WHERE column1 = '' OR column1 IS NULL
3:18 pm on Apr 7, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5628
votes: 47


why not ...

Because that would set the value for EVERY column1 (when blank or null), regardless of the value of column2. If the OP only wants to change the rows where column2 equals value1, but not where column2 equals some other value, code will do what is needed. For changing EVERY column1, your code is the correct method.

Reading the OP again, I see that I focused on the query, not the text explanation. Good catch!
7:37 pm on Apr 7, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


To add to the mix . . . if you set up your database correctly, you will have an expected value for uninitialized field.s That is, if NULL does it for you, you need only test for null.

UPDATE table SET
column1 = 'value1'
WHERE column2 = 'value1' AND column1 IS NULL;

If there's a possibility you'd insert a blank string in there, then it will no longer be NULL, so go with the or . . .
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members