update multiple columns in one sql statements



I am almost sure I can update variables columns in one select/case type
statement, but having problems working out the syntax.
I have a table with transactions - with tran types as the key.
in this example, types = A,B,C ,D.
in this first example I am updating the sum of QTY to value t_A based on
tran types =A.
can I perform sub query/case to update with the same where clause
but for types B,C and D?? I also have to insert for specific lot numbers each sum values.

Create table #t_reconcile(
t_lot_number int not null,
t_A float,
t_B float,
t_C float,
t_D float)

insert #t_reconcile
select t.lot_number, sum(t.qty)
from i , t
where i._id = t.event_id
i.transaction_type = 'A'
group by t.lot_number
order by t.lot_number

0
beverlyharris
11/2/2004 2:04:01 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
625 Views

Similar Articles

[PageSpeed] 16

You can certainly update more than one column in an update statement and a case statement is the way to do it.  The select part of your statement needs to have as many columns as your insert part.

Something like:
select lot_number,
sum(case transaction_type when 'A' then qty else 0 end),
sum(case transaction_type when 'B' then qty else 0 end),
sum(case transaction_type when 'C' then qty else 0 end),
sum(case transaction_type when 'D' then qty else 0 end)
from ....

insert #t_reconcile
(t_lot_number, t_A, t_B , t_C, t_D)
select t.lot_number, sum(t.qty)
from i , t
where i._id = t.event_id
i.transaction_type = 'A'
group by t.lot_number
order by t.lot_number
0
pdraigh
11/2/2004 3:15:05 PM
thanks,

one of the commands is giving me a syntax error at the 'And' statement.
what this one section needs is sum positive numbers and negative numbers
in different columns.
where did this go wrong??
sum(case i.transaction_type when 'A' AND qty > 0 then qty else 0 end)
0
beverlyharris
11/2/2004 3:37:24 PM

Can't do the 'AND' like this
sum(case i.transaction_type when 'A' AND qty > 0 then qty else 0 end)
has to be like this when you have multiple conditions
sum(case when i.transaction_type = 'A' AND qty > 0 then qty else 0 end)
0
pdraigh
11/2/2004 7:05:47 PM
Reply:

Similar Artilces:

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

SQL,Make Multiple values for multiple columns into one column separated by comma
Hi, I'm getting the result as this Name       Class       Subject------------------------------------------Anju         10             MathsAnju         10             Physics How to write the query to get the result like this Name       Class       Subjects------------...

How to make a long sql statement on Multiple lines instead of one long statement on one line
what code would i use to put this update statement on more than one lines. I can't figure it out set10 = "UPDATE " + databasenow + " SET 1= '" + 1.ToString + "', 2= '" + 2.ToString + "', 3= '" + 4.ToString + "', 5= '" + 5.ToString + "', 6= '" + 6.ToString + "', 7= '" + 7+ "' WHERE (new= '" + new.tostring+ "') "  set10 = "UPDATE " +  _ databasenow + _ " SET 1= '" + 1.ToString + "', 2= '" + 2...

Display multiple columns in Gridview from One Column of SQL table
Hi I have a table in sql with 2 columns and I want to display that in multiple columns in Gridview. SQL Table No.            Type 1               A1 1               B2 2              A2 2               B2 3        &...

multiple sql statements in one
Hi, I would like to send multiple sql statement in one statement to my db: so BEGIN TRAN INSERT (BLAH BLA..) SELECT @@identity COMMIT and send all that at once and then operate just get the query id. I use open client (php) to do this. Is anyone else done this, or has anyideo about it generally. Thanks Damjan ...

Multiple SQL Statements in one SQLCommand
I am just wondering if it is possible using SQL Server 2000 to have multiple SQL Statements executed with one sqlComm.ExecuteNonQuery(); call?RateMyEverything Absolutely.  You can just string them together.  (Or you can separate them with semicolons for clarity.)Terri MortonEngagement Manager, NeudesicHow to ask a question...

Stored Procedures
I am trying to create a StoredProcedure (below) that requires more than one SQL statement to be executed. I am getting a batch error as follows: Result set not permitted in '<batch statement>'. I am using ASA9 - version 9.0.1751. The following is the stored procedure that I am trying to execute. ALTER PROCEDURE "DBA"."asi_SDSelectUserDetailsRange" ( @lrange varchar(5), @hrange varchar(5), @Filter varchar(20), @ColumnName varchar(20) ) BEGIN DECLARE @SQL1 varchar(8000); DECLARE @SQL2 varchar(8000); DECLARE @SQL3 varchar(8000); D...

DBD::Oracle multiple SQL statements
DBI-Users, The DBI prepare statement documentation = http://search.cpan.org/author/TIMB/DBI-1.32/DBI.pm#prepare says prepare = "Prepares a single statement for later execution by the database engine" The DBD::Oracle PL/SQL examples = http://search.cpan.org/author/TIMB/DBD-Oracle-1.12/Oracle.pm#PL_SQL_Examp= les illustrates multiple statements in a SQL string. When I try to test this the following error from 'plsql_errstr' = indicates that it is not possible to have multiple statements in a SQL = string. The PL/SQL statements are not being treated individually...

Reuse derived columns in same SQL Select statement under SQL Server 2005
Hello, Our agency is in the process of migrating our Sybase Adaptive Server Anywhere 7.0 databases to Microsoft SQL Server 2005. We are currently using PowerBuilder 10.5. We have several datawindows that reuse SQL computed fields (derived columns) in the same SQL Select statement. Here is an example: select institution_code, incident_number, sequence, final_review_date, ts_status_code, idoc_number, (CASE WHEN coalesce(s.recommended_verbal,'N') <> coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal, (CASE WHEN coalesce(s.recomme...

SQL DataSource updates all but one field (and no error!)
This is a very wierd problem. I have a DetailsView populated by a select from a single table in a SQL 2000 database. The purpose of the DetailsView, of course, is to display existing data, edit existing data, or add a new record. All works well except Update. The update will save the row back to the database with new data in any or all field except one, not counting the key field, of course. I have changed the SQLDataSource from SQL statements for Select, Update, Insert, etc. to Stored Procedures and back again with no change in behavior. I can run the exact same Update statement in Query A...

How to Perform Multiple Updates in One SQL in Sybase
Hello Everyone: Sybase SQL Server 11.1 on a DEC/Alpha running Open VMS I have a table called, tblTest with the following fields: FieldName FieldType mt1 Number mt2 Number mt3 Number I have to SET all the fields values in tblTest to 2 if the value is 1. Update tblTest Set mt1 = 2 where mt1 = 1 Update tblTest Set mt2 = 2 where mt2 = 1 Update tblTest Set mt3 = 2 where mt3 = 1 Is it possible to perform the above operation with Just One SQL rather than running update 3 times. thanks addi Addi, ...

How to execute multiple SQL statements from one file??
I have a need to create an SQL Database - and then create all the Tables, indexes, etc. I have the SQL in a Tex file - and normally would just open up MS SQL Studio and run the script right there. That way works fine. However, need to be able to generate the DB from within my application. Yet - when I try to executet the commands in the file from Delphi - it gives me errors about the GO statements between each command. When I remove those - I get some other errors - I am looking at how to resolve. How do others out there execute a large number of SQL statements in one EXECSQL...

Query multiple databases with one sql statement?
Hi, Is it possible to query mutiple databases with one sql statement? Currently, I use SQL Query Analyzer to execute one sql statement that = queries tables from two MS SQL databases. I can make connections to = (and query) both databases using Perl and DBI, but I would like to query = both databases with one sql statement. =20 Below is a copy of the code I use with SQL Query Analyzer. Thanks in advance, Joel =20 SELECT=09 SecondDatabase.dbo.Table1.FieldA, dbo.Table2.FieldB,=20 dbo.Table3.FieldC,=20 RTRIM(LEFT(dbo.Table3.FieldC, 1)) AS NewHeader1,=20 dbo.Table4.FieldD ...

Multiple SQL's in one statement
Hi guys I was wondering if you could help me. I am currently using DBI against a Sybase Database. Is it possible to execute multiple SQL statements in a given statement handle ? For example, I would like to multiple views in a database. I would be most grateful. Thanks in advance Tony --=-Xl/xNtdgfMABNCHbPr8l Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Mon, 2002-05-13 at 05:24, Ho, Tony wrote: > Hi guys > I was wondering if you could help me. > I am currently using DBI against a Sybase Database. > Is it possible to execute multiple ...

Web resources about - update multiple columns in one sql statements - asp.net.sql-datasource

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from July 2011 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Peyton Manning Issues Statement Denying Doping Allegations - Bleacher Report
Denver Broncos quarterback Peyton Manning has issued a statement strongly denying that he received human growth hormone from an anti-aging clinic ...

Madonna says Sean Penn never assaulted her in court statement, denying previous reports
... of the allegations that have surfaced over the years accusing Sean of incidents of physical assault and abuse against me," she said in a statement ...

Statement from Premier League’s Crystal Palace Football Club
Crystal Palace Football Club is delighted to announce that it has today signed a deal with investors Josh Harris and David Blitzer. The deal ...

What I like to see: Agencies Issue Statement on CRE Loans
... doesn't suggest a problem in CRE lending, rather the agencies are trying to get ahead of future problems. From the FDIC: Agencies Issue Statement ...

Chicago police statement on double fatal police shooting - Chicago Tribune
Chicago police emailed the following statement to the media at about 9:45 the morning of Dec. 26, 2015, after an early morning police-involved ...

What changed in new Fed statement
This is a comparison of today's FOMC statement with the one issued after the Fed's previous policy-making meeting on Oct. 28.

Does Draymond feel Warriors have chance to make statement vs Cavs?
... Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch Does Draymond feel Warriors have chance to make statement ...

Rajon Rondo's Statement Looks A Lot Like Kobe Bryant's Statement
... pay after he called referee Bill Kennedy a “faggot” in a Dec. 3 game . (Kennedy publicly came out after the incident.) Rondo tweeted a statement, ...

Resources last updated: 12/28/2015 1:44:08 PM