homepage Welcome to WebmasterWorld Guest from 54.205.207.53
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
SQL Update
Column where Column = Column
Alternative Future

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4092379 posted 10:14 pm on Mar 5, 2010 (gmt 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

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4092379 posted 12:06 am on Mar 6, 2010 (gmt 0)

Try:
UPDATE table SET
column1 = 'value1'
WHERE column2 = 'value1' AND (column1 = '' OR column1 IS NULL);

Alternative Future

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4092379 posted 10:26 am on Mar 6, 2010 (gmt 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

bizminder



 
Msg#: 4092379 posted 9:41 am on Apr 7, 2010 (gmt 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.

topr8

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



 
Msg#: 4092379 posted 12:47 pm on Apr 7, 2010 (gmt 0)

why not

update table
set column1=column2
WHERE column1 = '' OR column1 IS NULL

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4092379 posted 3:18 pm on Apr 7, 2010 (gmt 0)

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!

rocknbil

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



 
Msg#: 4092379 posted 7:37 pm on Apr 7, 2010 (gmt 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 . . .

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved