Welcome to WebmasterWorld Guest from 107.20.104.161

Forum Moderators: open

Message Too Old, No Replies

SQL Update

Column where Column = Column

     

Alternative Future

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

WebmasterWorld Senior Member 10+ Year Member



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

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

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



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

Alternative Future

10:26 am on Mar 6, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

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

5+ Year Member



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

12:47 pm on Apr 7, 2010 (gmt 0)

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



why not

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

LifeinAsia

3:18 pm on Apr 7, 2010 (gmt 0)

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



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

7:37 pm on Apr 7, 2010 (gmt 0)

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



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 . . .
 

Featured Threads

Hot Threads This Week

Hot Threads This Month