dzirkelb
asked on
Help with updating an entire table with one query
I have a table where some old code was being used to store quotes, and I have fixed the code so it stores data correctly. But, now I need to clean up the table. Basically, I need to search the entire table, all fields, and replace ~~ with " wherever it is found.
I can do this one at a time like Update Table Set Field = replace(Field, '~~', '"'), but I dont' want to do this 100 times with different field names.
Any ideas on how to do it at once?
I can do this one at a time like Update Table Set Field = replace(Field, '~~', '"'), but I dont' want to do this 100 times with different field names.
Any ideas on how to do it at once?
do it using recordset
dim rs as dao.recordset, j as integer
set rs=currentdb.openrecordset ("tablex")
do until rs.eof
rs .edit
for j=0 to rs.fields.count-1
rs(rs(j).name)=replace(rs( j),"~~","' '")
next
rs.update
rs.movenext
loop
rs.close
dim rs as dao.recordset, j as integer
set rs=currentdb.openrecordset
do until rs.eof
rs .edit
for j=0 to rs.fields.count-1
rs(rs(j).name)=replace(rs(
next
rs.update
rs.movenext
loop
rs.close
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
is the table linked to Access from SQL Server,or is it an Access table?
In Access you could set up a loop, something like:
NOTE: do this with a copy of the table, not the original
In Access you could set up a loop, something like:
Dim rs as DAO.Recordset
Dim fld as DAO.Field
Dim strSQL as string
'This will open an empty recordset but will contain all of the field names and types
set RS = "SELECT * FROM yourTableName WHERE 0 = 1"
For each fld in rs.fields
if fld.Type = 10 or fld.Type = 12 Then 'text or memo
strSQL = "UPDATE yourTableName " _
& "SET [" & fld.name & "] = Replace([" & fld.name & "],'~~', '') " _
& "WHERE InStr([" & fld.name & "],'~~')>0
currentdb.execute strsql, dbfailonerror
end if
Next fld
This should loop through all of the fields in your table and only execute the update on those fields that are text or memo fields.NOTE: do this with a copy of the table, not the original
That's what I get for walking away for a couple of minutes.
I suggest dynamically generating the SQL statement, like this:
DECLARE @table_name varchar(257)
DECLARE @sql varchar(max)
DECLARE @sql2 varchar(max)
SET @table_name = 'dbo.<your_table_name>'
SET @sql = 'UPDATE ' + @table_name + CHAR(13) + CHAR(10) + 'SET '
SELECT @sql2 = STUFF((
SELECT
CAST(', [' AS varchar(max)) + CAST(c.name AS varchar(128)) + '] = ' +
'REPLACE([' + CAST(c.name AS varchar(128)) + '], ''~~'', ''"'')'
FROM sys.columns c
INNER JOIN sys.types t ON
t.system_type_id = c.system_type_id AND
t.user_type_id = c.user_type_id
WHERE
c.object_id = OBJECT_ID(@table_name) AND
--c.name NOT IN ('col1', 'col2', ...) --list of col names to exclude from UPDATE
t.name LIKE '%char%' AND
c.max_length >= 4
ORDER BY c.object_id, c.column_id
FOR XML PATH('')
), 1, 1, '')
SET @sql = @sql + @sql2
SELECT @sql
--EXEC(@sql) --uncomment when ready to run
DECLARE @table_name varchar(257)
DECLARE @sql varchar(max)
DECLARE @sql2 varchar(max)
SET @table_name = 'dbo.<your_table_name>'
SET @sql = 'UPDATE ' + @table_name + CHAR(13) + CHAR(10) + 'SET '
SELECT @sql2 = STUFF((
SELECT
CAST(', [' AS varchar(max)) + CAST(c.name AS varchar(128)) + '] = ' +
'REPLACE([' + CAST(c.name AS varchar(128)) + '], ''~~'', ''"'')'
FROM sys.columns c
INNER JOIN sys.types t ON
t.system_type_id = c.system_type_id AND
t.user_type_id = c.user_type_id
WHERE
c.object_id = OBJECT_ID(@table_name) AND
--c.name NOT IN ('col1', 'col2', ...) --list of col names to exclude from UPDATE
t.name LIKE '%char%' AND
c.max_length >= 4
ORDER BY c.object_id, c.column_id
FOR XML PATH('')
), 1, 1, '')
SET @sql = @sql + @sql2
SELECT @sql
--EXEC(@sql) --uncomment when ready to run
Try this script (replace TableNameHere with your real table name):
DECLARE @CommUPD NVARCHAR(MAX)=N'UPDATE TableNameHere SET '
SELECT @CommUPD = @CommUPD + N'' + c.name + ' = REPLACE(' + c.name + ',''~~'',''''),' + CHAR(13) + CHAR(10)
FROM sys.objects o
INNER JOIN sys.columns c ON c.object_id=o.object_id
WHERE o.name='TableNameHere' AND c.system_type_id IN (167, 175) -- varchar and char
SET @CommUPD = LEFT(@CommUPD, LEN(@CommUPD)-3) --Get rid of the last comma
EXEC sp_executesql @CommUPD
dim db as dao.databse, rs as dao.recordset, usql as string, j as integer, strFld as stringThis solution, apart from not being the best performance (need to select all rows to know the fields name and then will execute one update for each column) it's also not preventing error. How will deal with non text fields?
set rs=db.openrecordset("select * from tablex")
for j=0 to rs.fields.count-1
strfld=rs(j).name
usql="Update tableX set [" & strfld & "]= replace([" & strfld & "],'~~','''')
db.execute usql,dbfailonerror
next
rs.close
<(need to select all rows to know the fields name and then will execute one update for each column)>
first of all the codes was typed in directly to the page.
to get only the field names without opening the whole rtable as recordst
change this
set rs=db.openrecordset("selec t * from tablex")
with
set rs=db.openrecordset("selec t * from tablex where 1=0")
first of all the codes was typed in directly to the page.
to get only the field names without opening the whole rtable as recordst
change this
set rs=db.openrecordset("selec
with
set rs=db.openrecordset("selec
Are these 100+ fields the only char/varchar fields in the table?
Either way, we're talking dynamic SQL and a loop, whether it's looping through all columns that meet the above logic, or you build a table with all of these columns, and dynamic SQL uses that table.