Update multiple fields using a single query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Update multiple fields using a single query

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-25 : 15:28:41

Hello,

How are you today?

I wish to update all the blank fields in a table to state "No response". I could write a seperate update statement for each field:

update table1 set table1.country = 'No response' where table1.country =''

update table1 set table1.state = 'No response' where table1.state =''

But for 30 fields this would mean 30 queries. Is there anyway to do this in a single query?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-25 : 17:27:37

I would have to ask Why is this needed? because it seems to be a lot of work for what in return?

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-25 : 17:50:04

How about in the query that you use to display this information, do this manipulation? Storing "No response" takes up a lot more space that "".

Look at the queries where you get this data and try something like this (This might need to be massaged a bit for Access)

[CODE]
SELECT
CASE WHEN State = '' THEN 'No Response' ELSE State END AS State,
CASE WHEN country = '' THEN 'No Response' ELSE country END AS country
FROM Table1
[/CODE]

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-25 : 18:15:34

I agree with Michael. You should not store No Reponse in the database due to space and performance. By doing it his way, your query returns No Response where applicable but the column is empty in the database.

Tara

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-25 : 18:19:26

Hello,

Actually I am developing a routine to clean data:

1. I have an initial set of 1.5 Million records, which have blank values in some fields. For data mining purposes I need to flag such records with 'No response'.

2. For some of the fields I know the common errors that occur, so I have a look up table of the structure:

Error_Value Correct_value
U.S.A USA

so I would want to run an update query:

update table1 set table1.country = lookupTable.Correct_value where table1.country =lookupTable.Error_Value

Once again I don't want to write a whole set of queries to do this, I would want to 'walk' through the table to get the values for the update query.

Though that is an idea, I am writing this using VB (MS Access), so maybe one can have a table which contains a list of the fields, and each field can be accesed dynamically:

FieldTable:
Fieldname ValueToReplace ReplaceValue
Country No response
State No response

rst.MoveFirst
Do Until rst.EOF
DynFN = rst!Fieldname
DynVTR = rst!ValueToReplace
DynRV = rst!ReplaceValue

update table1 set table1.DynFN = DynRV where table1.DynFN = DynVTR

rst.MoveNext
Loop

Makes any sense? Isn't there a hidden object which contains the list of fields in a table?

I wouldn't want this to be overly difficuly, if it is too challenging then I can write the 30+ queries, just potentially creates a maintenance 'nightmare'.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-25 : 20:01:45

I've got a few ideas on how to handle this in SQL, but not in access. I know there is a way to access to column listing in SQL Server, but I don't know if that works in Access. You might have to do what you are talking about there. I was heading down that path...

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-25 : 20:03:52

Not exactly what you were looking for but close...


CREATE TABLE #LookUp(FieldName VARCHAR(50), ErrorValue VARCHAR(50), CorrectValue VARCHAR(50))
INSERT INTO #LookUp(FieldName, ErrorValue, CorrectValue) VALUES('Country', 'U.S.A', 'USA')
INSERT INTO #LookUp(FieldName, ErrorValue, CorrectValue) VALUES('Country', '', 'No Response')
INSERT INTO #LookUp(FieldName, ErrorValue, CorrectValue) VALUES('State', 'Alabama', 'AL')
INSERT INTO #LookUp(FieldName, ErrorValue, CorrectValue) VALUES('State', 'Texas', 'TX')
INSERT INTO #LookUp(FieldName, ErrorValue, CorrectValue) VALUES('State', '', 'No Response')


CREATE TABLE #BadData(Country VARCHAR(50), State VARCHAR(50))

INSERT INTO #BadData(Country, State) VALUES('U.S.A', 'Alabama')
INSERT INTO #BadData(Country, State) VALUES('U.S.A', '')
INSERT INTO #BadData(Country, State) VALUES('USA', '')
INSERT INTO #BadData(Country, State) VALUES('USA', 'Texas')
INSERT INTO #BadData(Country, State) VALUES('USA', 'CA')
INSERT INTO #BadData(Country, State) VALUES('', 'MD')

SELECT *, 'Before' FROM #BadData

--You still need an update for each field, 
--but maybe someone can help you find a better way than this
UPDATE #BadData 
SET Country = l.CorrectValue
FROM #Lookup l
INNER JOIN #BadData b ON b.Country = l.ErrorValue

UPDATE #BadData 
SET State = l.CorrectValue
FROM #Lookup l
INNER JOIN #BadData b ON b.State = l.ErrorValue

SELECT *, 'After' FROM #BadData

DROP TABLE #BadData
DROP TABLE #Lookup

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-26 : 07:06:33

Hello,

I wound up using the VB approach to avoid writing a whole set of update statements.

One note, I had Nulls in my fields instead of '', so:

Set Fieldsqry = db.CreateQueryDef("", _
"update [table1] set [table1]." & DynFN & " = '" & DynRV & "' where [table1]." & DynFN & " is Null ")
Fieldsqry.Execute (dbOpenDynaset)
Fieldsrst.MoveNext

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources