4

T SQL Deletes records based on a calculated field

 2 years ago
source link: https://www.codesd.com/item/t-sql-deletes-records-based-on-a-calculated-field.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

T SQL Deletes records based on a calculated field

advertisements

I want to delete rows in my table where the answer for 6 fields is zero / null:

Delete
from TableA
WHERE (a + b + c + d + e + f) IS NULL

Thanks

Try this

DELETE T FROM
  dbo.TableA T
  WHERE COALESCE(a, 0) +
        COALESCE(b, 0) +
        COALESCE(c, 0) +
        COALESCE(d, 0) +
        COALESCE(e, 0) +
        COALESCE(f, 0) = 0

See this SQLFiddle

Tags tsql

Related Articles

sql delete records based on the combination of several fields in the other table

I realize the tables have the same two columns but I'm working with inflexible constraints so I can't change this structure. TableA: Host_ID | Carrier_ID 111 | 222 /*Row 1*/ 111 | 333 /*Row 2*/ TableB: Host_ID | Carrier_ID 111 | 222 I am working on a

SQL - Retrieve records based on parameters where either parameter can be null

I have a stored procedure which takes in five parameters of which two can be null - we will call these parameters A and B What I would like to do is select records based on the following logic. If Parameter A is NULL then only return records that mat

SQL - Updating records based on the most recent date

I am having difficulty updating records within a database based on the most recent date and am looking for some guidance. By the way, I am new to SQL. As background, I have a windows forms application with SQL Express and am using ADO.NET to interact

How to update a set of records based on two different field values

I am having a really difficult time conceiving how to do the following. Basically I have a table such as the following with a two key fields (an id field and another field with a numerical identifier... ID fldNumData fldData 49562 1 1.145816 49562 2

What is the right way to insert, update, or delete records based on an external source?

I have two externally hosted third-party .txt files that are updated on an irregular basis by someone other than myself. I have written a script that pulls this information in, manipulates it, and creates a merged array of data suitable for use in a

SQL: delete records with invalid foreign keys

I just found a bug in my Rails app which would lead to certain records not getting deleted with their associated objects. The bug has been fixed, but I now have several thousand records in my database with foreign key values pointing to records that

Oracle sql retrieves records based on maximum time

i have below data. table A id 1 2 3 table B id name data1 data2 datetime 1 cash 12345.00 12/12/2012 11:10:12 1 quantity 222.12 14/12/2012 11:10:12 1 date 20/12/2012 12/12/2012 11:10:12 1 date 19/12/2012 13/12/2012 11:10:12 1 date 13/12/2012 14/12/201

SQL - selecting records based on the number of associated entries in a separate table

So I'm being particularly dense I'm sure - but I'm struggling with what should be a simple (mysql) SQL statement in PHP. I have two tables: entry containing the name & details of a competition entry. votes containing a timestamp, unique ID, and entry

MySQL Query to delete records based on their status on a second table

I have two tables: CREATE TABLE qr_code ( id int(10) primary key auto_increment, code int(10) not null ); CREATE TABLE area_qrcode ( id int(10) primary key auto_increment, area_id int(10) not null, qr_code_id int(10) not null, status set('open', 'clo

Delete records based on matrix list - LINQ

I have struggled a lot untill I reached this step. Basically, finally after certain criteria, I have an array with the ID's that I should remove from the DB. This is the code that I have: var ListOfIdThatNeedToBeRemoved = {id's}; I also have a table

Oracle SQL-Flag records based on record date vs. history

This is my 1st post on the forum. Usually I was able to find what I needed - but to tell the truth - I am not really sure how to ask a correct question to the issue. Therefore, please accept my apologies if there already is an answer on the forum and

Oracle: How do I determine if there are gap records based on a date field?

I have an application that manages employee time sheets. My tables look like: TIMESHEET TIMESHEET_ID EMPLOYEE_ID etc TIMESHEET_DAY: TIMESHEETDAY_ID TIMESHEET_ID DATE_WORKED HOURS_WORKED Each time sheet covers a 14 day period, so there are 14 TIMESHEE

Select the records based on what a field starts?

I have a database with a table that has identifiers c1, c2, c3..etc.. Instead of writing a query that has a bunch of ORs in it, how can I modify the below query with something that will catch all the records that begin with a certain letter? SELECT P

How to get the most recent record based on the last field modified in JAP (session implementation) in hibernation

I've been scratching my head over the past 3 hours to find the most recent object using hibernate.i've made attempts but always returns either null, or castTypeExcetpion. public Content getMostRecentByCategoryAndGenre(Category category, Genre genre){

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK