Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with updating an entire table with one query

Posted on 2014-12-17
9
Medium Priority
?
173 Views
Last Modified: 2014-12-17
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?
0
Comment
Question by:dzirkelb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40504838
Do all of these 100+ fields have some kind of common prefix/suffix/whatever?
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40504850
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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40504861
or

dim db as dao.databse, rs as dao.recordset, usql as string, j as integer, strFld as string
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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 48

Expert Comment

by:Dale Fye
ID: 40504891
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:

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

Open in new window

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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40504893
That's what I get for walking away for a couple of minutes.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40504908
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
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40504920
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 

Open in new window

0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40504960
dim db as dao.databse, rs as dao.recordset, usql as string, j as integer, strFld as string
 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
This 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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40504986
<(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("select * from tablex")
with
 set rs=db.openrecordset("select * from tablex where 1=0")
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question