Update Null value based on next record value

Question

Hi,

I have a Table as 

Date Name InvNo
10/05/2012 aaaa NULL
10/05/2012 aaaa 111
10/05/2012 aaaa 111
10/05/2012 bbb NULL
10/05/2012 bbb 456
10/05/2012 bbb 456

I want to update Null invno values with same name/date values INVNO from next records.

---

Virendra Yaduvanshi http://wikidba.wordpress.com/

Answer 1

The solution depends on SQL Server version, I think.

Take a look at the last problem described in this blog

http://beyondrelational.com/modules/2/blogs/78/posts/11137/interesting-t-sql-problems.aspx

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 2

See Naomi's post. But for my version (SQL Serv 2008 R2), I'd go with this:-

;WITH CTE AS (
SELECT DATE, NAME, INVNO, ROW_NUMBER(OVER ORDER BY NEWID()) AS ID FROM TABLE_NAME)
, WITH CTE_PLUSONE AS (
SELECT INVNO, ID FROM CTE
WHERE ID IN (SELECT ID+1 FROM TABLE_NAME WHERE INVNO IS NULL))
UPDATE TABLE_NAME
SET INVNO=B.INVNO
FROM TABLE_NAME AS A
JOIN CTE_PLUSONE AS B
ON A.ID+1=B.ID

It's untested, so more than likely it'll need tweaks, but the idea is to select your result set with a row marker; then get the INVNOs based on the null values, then update your table.

Answer 3

I want to update Null invno values with same name/date values INVNO from next records.

you want to update null with same name or date OR name and date values?

---

Best Luck, Shenoy

Answer 4

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed completely!). Temporal data should use ISO-8601 formats (failed again). Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. This is total crap! You have duplicate rows, so this is a deck of punch cards. And you were too rude and lazy to even try with some DDL. WHY? 

CREATE TABLE Stupid_Invoices 
(invoice_date DATE NOT NULL, 
 customer_name CHAR(4) NOT NULL, 
 invoice_nbr CHAR(4), 
 PRIMARY KEY (???impossible!!????));

INSERT INTO Invoices
VALUES 
('2012-10-05', 'Aaaa', NULL), 
('2012-10-05', 'Aaaa', 111), 
('2012-10-05', 'Aaaa', 111), 
('2012-10-05', 'Bbbb', NULL), 
('2012-10-05', 'Bbbb', 456), 
('2012-10-05', 'Bbbb', 456);

>> I want to update NULL invoice_nbr values with same name/date values invoice_nbr from next [sic] records [sic] <<

You do not even know that rows are not records! You do not know that tables have no ordering so “next record” is like talking about “wet fire” or other silliness.  Try this slightly better design. 

 CREATE TABLE Redundant_Invoices 
(invoice_date DATE NOT NULL, 
 customer_name CHAR(4) NOT NULL, 
 invoice_nbr CHAR(4) NOT NULL, 
 occurences SMALLINT DEFAULT 1 NOT NULL 
 CHECK (occurences > 0),
 PRIMARY KEY ((invoice_date,  customer_name,  invoice_nbr)

Try again, after you have learned a few of the basic concepts of RDBMS and manners. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

Hi,

Try below, though I could not understand the logic for your requirement.

create table tb1
(dt date default getdate(), name varchar(50), invno varchar(40));
insert into tb1 values (default, 'aaa', null)
insert into tb1 values (default, 'aaa', '111')
insert into tb1 values (default, 'aaa', '111')
insert into tb1 values (default, 'bbb', null)
insert into tb1 values (default, 'bbb', '456')
insert into tb1 values (default, 'bbb', '456')
update tb1 set invno = b.invno from
(select * from tb1 where invno is null) a inner join
(select * from tb1 where invno is not null) b on a.dt = b.dt and a.name = b.name
where tb1.invno is null

---

Best Luck, Shenoy