Update Query needed

  • Thread starter Thread starter Bikini Browser
  • Start date Start date
B

Bikini Browser

Hello...

I need a query written that will do the following...

If column A is NULL and Column B is NOT Null, Then move (not copy) column B
to Column A.


Any takers? How do you write that?

Dale
 
Have you tried?

UPDATE YourTable
SET ColumnA = ColumnB,
ColumnB = Null
WHERE ColumnA is Null and ColumnB is Not Null

Test this on a copy of your data and see if it gives you the expected
results. Or BACK UP your data first and then try it.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
I tried it. It did not work. 233 rows were changed but I don't know what
those chanages were. Most of the data is still the same.

No error messages.

Hmmm...

Dale
 
what does this return?

SELECT count(*)
FROM yourTable
WHERE ColumnA is null and ColumnB is not null
 
It returns 233 records.

BUT I looked at the data after that first query was ran and there were MANY
records that remained without data in columnA and with data in columnB.

What could be happening?

Dale
 
Does the query below return 233 after you run the Update Query?

SELECT count(*)
FROM yourTable
WHERE ColumnA is null and ColumnB is not null

Did you get the column names correct? What are your actual column
names? Post the actual SQL that you are using.
 
Why do you say it did not work? Were both fields not updated?

Also, I thought you were just giving us a generic question, so I gave you a
generic response. At a minimum I should have added brackets around the
source field name in the line SET ColumnA = ColumnB

UPDATE YourTable
SET ColumnA = [ColumnB] ,
ColumnB = Null
WHERE ColumnA is Null and ColumnB is Not Null

Please copy and post the SQL of your actual query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
UPDATE testback

SET Contactlastname = [Contactsecondlastname] ,

Contactsecondlastname = Null

WHERE Contactlastname is Null and Contactsecondlastname is Not Null



returns 233 records from 13000 records. about 25% need to be updated.



Dale
 
UPDATE testback

SET Contactlastname = [Contactsecondlastname] ,

Contactsecondlastname = Null

WHERE Contactlastname is Null and Contactsecondlastname is Not Null





John Spencer said:
Why do you say it did not work? Were both fields not updated?

Also, I thought you were just giving us a generic question, so I gave you
a generic response. At a minimum I should have added brackets around the
source field name in the line SET ColumnA = ColumnB

UPDATE YourTable
SET ColumnA = [ColumnB] ,
ColumnB = Null
WHERE ColumnA is Null and ColumnB is Not Null

Please copy and post the SQL of your actual query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Bikini Browser said:
I tried it. It did not work. 233 rows were changed but I don't know what
those chanages were. Most of the data is still the same.

No error messages.

Hmmm...

Dale
Click to expand...
Click to expand...
 
If more than those 233 need to be updated then either you have records
that have contactlastname and contactsecondlastname both null or the
value that is in contactlastname is not null. contactlastname may
actual contain '' or ' ' or ' ' for that matter.

Try this and see if it yields the right result:

SELECT *
FROM testback
WHERE (Contactlastname is Null OR Contactlastname = '') and
Contactsecondlastname is Not Null

Cheers,
Jason Lepack

UPDATE testback

SET Contactlastname = [Contactsecondlastname] ,

Contactsecondlastname = Null

WHERE Contactlastname is Null and Contactsecondlastname is Not Null

returns 233 records from 13000 records. about 25% need to be updated.

Dale




Does the query below return 233 after you run the Update Query?
Click to expand...
SELECT count(*)
FROM yourTable
WHERE ColumnA is null and ColumnB is not null
Click to expand...
Did you get the column names correct? What are your actual column
names? Post the actual SQL that you are using.
Click to expand...

- Show quoted text -
Click to expand...
 
I suspect that the field ContactLastName has a space or spaces in it. Given
that, one way to handle the situation follows. This can be slow since the
where clause will not be able to use any index that might exists on
ContactLastName.

UPDATE testback
SET Contactlastname = [Contactsecondlastname] ,
Contactsecondlastname = Null
WHERE Trim([Contactlastname] & "") = "" and Contactsecondlastname is Not
Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Bikini Browser said:
UPDATE testback

SET Contactlastname = [Contactsecondlastname] ,

Contactsecondlastname = Null

WHERE Contactlastname is Null and Contactsecondlastname is Not Null





John Spencer said:
Why do you say it did not work? Were both fields not updated?

Also, I thought you were just giving us a generic question, so I gave you
a generic response. At a minimum I should have added brackets around the
source field name in the line SET ColumnA = ColumnB

UPDATE YourTable
SET ColumnA = [ColumnB] ,
ColumnB = Null
WHERE ColumnA is Null and ColumnB is Not Null

Please copy and post the SQL of your actual query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Bikini Browser said:
I tried it. It did not work. 233 rows were changed but I don't know
what those chanages were. Most of the data is still the same.

No error messages.

Hmmm...

Dale

Have you tried?

UPDATE YourTable
SET ColumnA = ColumnB,
ColumnB = Null
WHERE ColumnA is Null and ColumnB is Not Null

Test this on a copy of your data and see if it gives you the expected
results. Or BACK UP your data first and then try it.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Bikini Browser wrote:
Hello...

I need a query written that will do the following...

If column A is NULL and Column B is NOT Null, Then move (not copy)
column B to Column A.


Any takers? How do you write that?

Dale
Click to expand...
Click to expand...
Click to expand...
 
I just wanted to thank you because your query worked and I am happy. These
news groups work because of people like you.

Thanks again...

Dale Allen

PS Happy Easter!


John Spencer said:
I suspect that the field ContactLastName has a space or spaces in it.
Given that, one way to handle the situation follows. This can be slow
since the where clause will not be able to use any index that might exists
on ContactLastName.

UPDATE testback
SET Contactlastname = [Contactsecondlastname] ,
Contactsecondlastname = Null
WHERE Trim([Contactlastname] & "") = "" and Contactsecondlastname is Not
Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Bikini Browser said:
UPDATE testback

SET Contactlastname = [Contactsecondlastname] ,

Contactsecondlastname = Null

WHERE Contactlastname is Null and Contactsecondlastname is Not Null





John Spencer said:
Why do you say it did not work? Were both fields not updated?

Also, I thought you were just giving us a generic question, so I gave
you a generic response. At a minimum I should have added brackets
around the source field name in the line SET ColumnA = ColumnB

UPDATE YourTable
SET ColumnA = [ColumnB] ,
ColumnB = Null
WHERE ColumnA is Null and ColumnB is Not Null

Please copy and post the SQL of your actual query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I tried it. It did not work. 233 rows were changed but I don't know
what those chanages were. Most of the data is still the same.

No error messages.

Hmmm...

Dale

Have you tried?

UPDATE YourTable
SET ColumnA = ColumnB,
ColumnB = Null
WHERE ColumnA is Null and ColumnB is Not Null

Test this on a copy of your data and see if it gives you the expected
results. Or BACK UP your data first and then try it.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Bikini Browser wrote:
Hello...

I need a query written that will do the following...

If column A is NULL and Column B is NOT Null, Then move (not copy)
column B to Column A.


Any takers? How do you write that?

Dale
Click to expand...
Click to expand...
Click to expand...
 
Back
Top