I have the following table Table1 :
numseq Article ID Num1 Num2 Num3 Num4 Col07 Col08
3023 F 242840794 4 12 1 13 RCN03 9
3023 F 242840794 4 12 1 13 RCN03 9
3023 F 242840794 4 12 1 13 RCN03 9
The value of Col07 is false, there are one 5 character instead of 6.
Now, I have the corrected data in the table Table2
numseq Article ID Num1 Num2 Num3 Num4 Col07 Col08
3023 F 242840794 4 12 1 13 RCN031 9
3023 F 242840794 4 12 1 13 RCN035 9
3023 F 242840794 4 12 1 13 RCN037 9
the key to update Table1.Col07 are all the others columns.
How can do the update ?
I use Sybase IQ 15.1.
I tried with row_number(), but the function is not supported.
NOTES: I don't have access to a IQ database; I do have access to a SQLAnywhere database; the functions used below do show up in the IQ reference manual; the proposed solution works in my SQLAnywhere database; net result is that the following should work in IQ ... >>crossing fingers<< ...
First some background:
1 - each record in a table has a unique row id that can be accessed via the rowid(<table_name>) function
2 - the number() function can be used to generate a unique number (starting with 1) for a result set however, this function cannot be used in a sub-query or derived table (which means we can't incorporate derived tables in our UPDATE statement)
Gist of the solution:
1 - pull the desired rows from Table1 and Table2 into #temp tables, using the number() function to generate unique row numbers (starting from 1) for each set of #temp table records; also pull the rowid() for Table1
2 - write our UPDATE statement to reference our #temp tables, joining unique pairs of #temp table rows based on their row numbers (generated by the number() function), and join back to the main table via the rowid() values
Setup:
create table Table1 (numseq int, Article char(1), ID int, Num1 int, Num2 int, Num3 int, Num4 int, Col07 varchar(10), Col08 int)
go
create table Table2 (numseq int, Article char(1), ID int, Num1 int, Num2 int, Num3 int, Num4 int, Col07 varchar(10), Col08 int)
go
insert Table1 values (3023,'F',242840794,4,12,1,13,'RCN03',9)
insert Table1 values (3023,'F',242840794,4,12,1,13,'RCN03',9)
insert Table1 values (3023,'F',242840794,4,12,1,13,'RCN03',9)
go
insert Table2 values (3023,'F',242840794,4,12,1,13,'RCN031',9)
insert Table2 values (3023,'F',242840794,4,12,1,13,'RCN035',9)
insert Table2 values (3023,'F',242840794,4,12,1,13,'RCN037',9)
go
numseq Article ID Num1 Num2 Num3 Num4 Col07 Col08
----------- ------- ----------- ----------- ----------- ----------- ----------- ---------- -----------
3023 F 242840794 4 12 1 13 RCN03 9
3023 F 242840794 4 12 1 13 RCN03 9
3023 F 242840794 4 12 1 13 RCN03 9
numseq Article ID Num1 Num2 Num3 Num4 Col07 Col08
----------- ------- ----------- ----------- ----------- ----------- ----------- ---------- -----------
3023 F 242840794 4 12 1 13 RCN031 9
3023 F 242840794 4 12 1 13 RCN035 9
3023 F 242840794 4 12 1 13 RCN037 9
Build/populate the #temp tables:
-- we'll use WHERE clauses in our 'select/into' statements in case you
-- are planning on processing just a subset of Table1 and/or Table2; and
-- while the 'order by' clauses aren't needed for this simple example, you
-- will need to include them if you plan on updating multiple sets of
-- duplicate rows at the same time (ie, you need to make sure the number()
-- values match between sets of duplicate rows)
select rowid(Table1) as rid, number() as nbr, * into #t1 from Table1 where numseq = 3023 and Article = 'F' and ID = 242840794 and Num1 = 4 and Num2 = 12 and Num3 = 1 and Num4 = 13 and Col08 = 9
order by numseq, Article, ID, Num1, Num2, Num3, Num4, Col08
go
select number() as nbr, * into #t2 from Table2 where numseq = 3023 and Article = 'F' and ID = 242840794 and Num1 = 4 and Num2 = 12 and Num3 = 1 and Num4 = 13 and Col08 = 9
order by numseq, Article, ID, Num1, Num2, Num3, Num4, Col08
go
select * from #t1
select * from #t2
go
rid nbr numseq Article ID Num1 Num2 Num3 Num4 Col07 Col08
-------------------- ----------- ----------- ------- ----------- ----------- ----------- ----------- ----------- ---------- -----------
68222976 1 3023 F 242840794 4 12 1 13 RCN03 9
68222977 2 3023 F 242840794 4 12 1 13 RCN03 9
68222978 3 3023 F 242840794 4 12 1 13 RCN03 9
nbr numseq Article ID Num1 Num2 Num3 Num4 Col07 Col08
----------- ----------- ------- ----------- ----------- ----------- ----------- ----------- ---------- -----------
1 3023 F 242840794 4 12 1 13 RCN031 9
2 3023 F 242840794 4 12 1 13 RCN035 9
3 3023 F 242840794 4 12 1 13 RCN037 9
And now the UPDATE:
update Table1
set Table1.Col07 = #t2.Col07
from Table1
join #t1
-- rowid() is unique and thus the only 'column' we need in the join between Table1 and itself (aka #t1)
on rowid(Table1) = #t1.rid
-- and the rest of the join columns between Table1 and #t1; optional
/*
and Table1.numseq = #t1.numseq
and Table1.Article = #t1.Article
and Table1.ID = #t1.ID
and Table1.Num1 = #t1.Num1
and Table1.Num2 = #t1.Num2
and Table1.Num3 = #t1.Num3
and Table1.Num4 = #t1.Num4
and Table1.Col08 = #t1.Col08
*/
join #t2
-- join on our number() values
on #t1.nbr = #t2.nbr
-- and the rest of the join columns between #t1 and #t2; optional but
-- provides an extra safety check to make sure we don't update records
-- with the wrong values (eg, number() is generated in wrong order)
and #t1.numseq = #t2.numseq
and #t1.Article = #t2.Article
and #t1.ID = #t2.ID
and #t1.Num1 = #t2.Num1
and #t1.Num2 = #t2.Num2
and #t1.Num3 = #t2.Num3
and #t1.Num4 = #t2.Num4
and #t1.Col08 = #t2.Col08
go
select * from Table1
go
numseq Article ID Num1 Num2 Num3 Num4 Col07 Col08
----------- ------- ----------- ----------- ----------- ----------- ----------- ---------- -----------
3023 F 242840794 4 12 1 13 RCN031 9
3023 F 242840794 4 12 1 13 RCN035 9
3023 F 242840794 4 12 1 13 RCN037 9