spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Update multiple row values to same row and different columns


This should work:

;with cte (rn, id, inst_id,
person_name, email) as (
    select row_number() over (partition by inst_id
order by id) rn, * 
    from person
    )

update f
set 
  person1 = cte1.person_name, 
  email1  = cte1.email, 
  person2 = cte2.person_name, 
  email2  = cte2.email
from firm f
left join cte cte1 on f.inst_id = cte1.inst_id and
cte1.rn = 1
left join cte cte2 on f.inst_id = cte2.inst_id and
cte2.rn = 2

The common table expression (cte) used as a source for the update numbers rows in the person table, partitioned by inst_id, and then the update joins the cte twice (for top 1 and top 2).

Sample SQL Fiddle


Categories : SQL

Related to : Update multiple row values to same row and different columns
Grouping with no repeating values in multiple columns
Here's one idea - but I'm not certain that (my)sql is the best tool for combinatorial problems... DROP TABLE IF EXISTS my_table; CREATE TABLE my_table (id1 INT NOT NULL,id2 CHAR(3) NOT NULL,PRIMARY KEY(id1,id2)); INSERT INTO my_table VALUES (100 ,'AAA'), (100 ,'BBB'), (200 ,'AAA'), (200 ,'BBB'), (300 ,'AAA'), (300 ,'BBB'), (300 ,'CCC'), (400 ,'BBB'); SELECT * FROM my_table x

Categories : Mysql
How to insert multiple rows record in multiple columns of single row in datagridview
Try this:- select rollno,name,fname,[8],[9],[28] FROM ( select rollno,name,fname, DATEPART(d,[date]) enterdate,attandance from students )first PIVOT ( MAX(attandance) FOR [enterdate] IN ([8],[9],[28]) ) as pivottable This is giving me following output:- You can then display this data in your gridview.

Categories : C#
insert multiple records into multiple columns of a table from many tables
Your query is syntactically correct. That doesn't mean it does what you want it to do. It could be that you have no values in ,c.[Child] ,a.[Child_Alias] ,o.[Child_Operator] for the records that meet the rest of the query conditions and thus null is the correct value. It could be that you have no valaues in the join tables for those fields but you should have values, in which case there is

Categories : SQL
MySql Trigger INSERT... ON DUPLICATE KEY UPDATE not recognising columns
You are referencing a table called data_import with no from clause. This is fixed using insert . . . select: INSERT INTO stock_pricing (`STOCK_ID`, `DATE`, `LAST_CLOSE_DOM_CURR`) SELECT DATA_IMPORT.STOCK_ID, DATA_IMPORT.DATE, DATA_IMPORT.ADJ_CLOSE FROM DATA_IMPORT ON DUPLICATE KEY UPDATE stock_pricing.STOCK_ID= DATA_IMPORT.STOCK_ID, stock_pricing.DATE= DATA_IMPORT.DATE, stock_pr

Categories : Mysql
multiple values how to get values into Action form
hey Dont worry Its Easy and i m Sure u will do it.. Firstly u wrap all data in Form tag in HTML and just form tag action use in call Action i.e actionname.action. and in that action have must required getter setter of that data. u get all data automatically..surprise... ?? yes its Done. i hope u got it.

Categories : Java
Recently Add
SQL to COUNT items associated with individuals
sqlite in syntax, by column
Get DISTINCT Sum of Total by WorkID with LEFT JOINs
Create a trigger dealing with two tables
SQL: Combinations by Type
T-SQL Procedure split and join on sub columns
GROUP BY in Informix (11.5)
Returning duplicates based on one value in SQL
Joins over non-key attributes
MS-Access SELECT TOP N subquery SQL
Rails PG::UndefinedTable: ERROR: missing FROM-clause entry for table
Case-insensitive primary key of type nvarchar where ß != ss
Get min and max value from a multiple rows events
JPA/Hibernate: on delete set null
How do i structure a query where I am INSERTing into a new table and need to increment a column every time?
sql query to groupby with a deduplicated column
Oracle left-outer-join syntax shorthand notation (+) available in HQL?
Selecting numbers into array and then looping over them
Update query to fix incorrect references between two tables
Merging query records into one
How to select duplicate columns data from table
Confused syntax in Where clause
SQL Server 2005 GROUP BY and COUNT query for each month
error in sql query result
Can IIS and SQL run on the same Azure Virtual Machine?
SQL query to find all names where same user_id count > 3
SQL return a default value if a row is not found [PostgreSQL]
Schema Design for Invoices and Transaction - "mystery" relation
sql create table same primary & foreign key
Adding FK constraint WITH CHECK (SQL Server)
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.