How do I apply an SQL statement to an entire table?

Question

5.00/5 (1 vote)

See more:

After reviewing a datatable, I realized that blank cells were not blanks at all. I looked for different statements that may help with this problem and finally found one.

here is the code:

LTRIM(RTRIM(ISNULL(col1,''))) = ''

so I set up this statement:

update mytable set col1 = '' where LTRIM(RTRIM(ISNULL([{0}],''))) = ''

how can I set this up to where it will affect the entire table instead of just a column at a time?

What I have tried:

tried using an and statement with the where statement, but that was literally stating a combination condidtion.

Posted 14-Dec-17 2:38am

Member 11856456

Updated 14-Dec-17 2:46am

Add a Solution

Comments

Richard Deeming 14-Dec-17 8:50am

Seems like a bad idea. There is a fundamental difference between Null (unknown) and an empty string (known to be blank).

Member 11856456 14-Dec-17 8:57am

I wish I didn't have to do this at all. this problem seems to stem from me importing an excel sheet into my datatable. This information looks like it has empty string values in certain cells, but when I look up the column = '' nothing comes up in the search. However, after I use the statement LTRIM(RTRIM(ISNULL([{0}],''))) = '' then it fixes that problem and it shows the cells as empty strings.

Richard Deeming 14-Dec-17 8:59am

Try WHERE column Is Null instead.

Member 11856456 14-Dec-17 9:09am

The where portion works, but why would I need to do any of this if the information being imported already is supposedly empty for certain cells? If I can understand the why maybe I can fix it before importing the data.

Richard Deeming 14-Dec-17 9:12am

How are you importing the data?

This thread[^] suggests that you need to use a derived column if you're using SSIS.

There's more information on SQL Server Central[^], but I'm not sure whether you need to be signed up to see it.

Member 11856456 14-Dec-17 9:16am

im using the SQL bulkcopy method. I will check this out to see if I can locate something useful in it to help this dilemma.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Leo Chapiro · Answer 1 · 2017-12-14T02:46:00

Take a look at this answer: sql - How to UPDATE all columns of a record without having to list every column - Stack Overflow[^]

It's not possible.

What you're trying to do is not part of SQL specification and is not supported by any database vendor. See the specifications of SQL UPDATE statements for MySQL, Postgresql, MSSQL, Oracle, Firebird, Teradata. Every one of those supports only below syntax:

SQL

UPDATE table_reference
   SET column1 = {expression} [, column2 = {expression}] ...
[WHERE ...]