How to make update from that select statement PL/SQL

Question

I need to convert this select:

select *
    from (select * from game where rownum <= 4 order by rownum desc)
  where rownum = 1;

to update but i don't know how i tried something like this

update game
   set x4 = 0
 where (select *
    from game
   where (select * from game where rownum <= 4 order by desc)
   where rownum = 1);

but it's completly wrong, i know... Any ideas or it's impossible to make in plsql?

what data is looks like (it's an example):

    x1  x2  x3  x4  x5  x6
1   2   1   6   2   2   1
2   2   2   3   3   2   3
3   5   5   3   2   3   2
4   5   4   4   4   5   5
5   4   3   4   1   2   6
6   1   2   2   5   2   2

what table i want after update (column x4 and row 4 changed into 0):

    x1  x2  x3  x4  x5  x6
1   2   1   6   2   2   1
2   2   2   3   3   2   3
3   5   5   3   2   3   2
4   5   4   4   0   5   5
5   4   3   4   1   2   6
6   1   2   2   5   2   2

im trying to make alghoritm to find the best way to destination, the point is that there is no id, but in sum up i think i will need to add it anyway because it's no way to make it on those rownums...

Please post some sample data and needed result. Looking at that `order by` and `rownum`, I believe that using a `select` to do an `update` is not the only issue here, — Aleksej, Jul 23 '18 at 12:22
@starko but the problem is that i need to use rownum because i don't see other way to take record "manualy". — Potato, Jul 23 '18 at 12:43
Now the question is: how do you decide that the row with id=4 is the fourth row? That is: how are the rows ordered? Consider that rows in a table have no order, so you have to explicit the ordering criteria — Aleksej, Jul 23 '18 at 12:53
@Aleksej rows are ordered by date of create (i guess), first row that was created is first second row that was added is second et cetera. Okey, i think there is no way to do that without id, so i don't want to complicate it more. Let's assume that your answer is this good one. — Potato, Jul 23 '18 at 13:11

Leocanis · Answer 1 · 2018-07-23T12:37:50.767

0

When you using subquery in where, you have to assign results of it to something. Your query should look like this.

update game g1
set x4 = 0
where g1.id in
(select g2.id
  from game g2
 where g2.id in
       (select * from (select g3.id from game g3 order by g3.id desc) g4 where rownum <= 4)
 and rownum = 1);

You need first order then you can use rownum.

edited Jul 23 '18 at 12:37

answered Jul 23 '18 at 12:24

Leocanis

303
2
11

Are you sure that `rownum` and `order by`, used that way, give something useful? – Aleksej Jul 23 '18 at 12:33
Yea, it needs a little bit of fix. – Leocanis Jul 23 '18 at 12:35
Now, it's working. And using rownum and order by in same query can help to select top results. https://stackoverflow.com/questions/2498035/oracle-select-top-10-records – Leocanis Jul 23 '18 at 12:45
the point is that i have no id column in my table i edited post to have knowledge how table is looks like. – Potato Jul 23 '18 at 12:50
You have order by something and in you can change to exists. – Leocanis Jul 23 '18 at 12:58

score 0 · Answer 2 · answered Jul 23 '18 at 13:20

You need a 'key' (ie. a column [set] whose values unambiguously identify a single record; if you cannot find one, better revisit your data model) and a sorting criterion for your records

Let's assume you have these and let's simplify things by encoding these as hypothetical columns id and sort1.

Then you could go about using the following merge statement to set the x4 column to 0 for the top 4 (according to your sorting criterion) records:

   MERGE INTO game g
        USING (
                 SELECT * FROM (SELECT * FROM game gs order by gs.sort1 desc) WHERE rownum <= 4
              ) src
           ON (
                 src.id = g.id
              )
 WHEN MATCHED
         THEN UPDATE SET g.x4 = 0
            ;

If you do not find a 'key', this solution potentially breaks as the update might include records who'd not meet the sorting criterion.

If your sorting criterion does not distinguish between items #4 and #5 in the top 4 list, your results may vary between queries ( but then top 4 wouldn't be well-defined anyway)

Update

In case you want to update a single record only, you can use a simple update with a subquery:

       UPDATE game g
          SET g.x4 = 0
        WHERE g.id = (
                  SELECT id FROM (SELECT * FROM game gs order by gs.sort1 desc) WHERE rownum = 4
              )
            ;

Demo

An SQL fiddle to toy around with here

score 0 · Answer 3 · answered Jul 23 '18 at 13:33

One thing I would like to clarify: you do not need an ID because you need a key for the update, even if it would be much better having one on your table, but you need something to use in ordering the rows, not necessarily a key.

Differently said, say you have an ID on your table, with a PK based on that column, but this ID is a random value; this would be unuseful to order rows and to do what you need.

What you need is some criteria to order rows and decide which row is the 4th one.

Say you have a creation date in you table, you can use that date to order the rows and rowid for the update

create table tabTest(createDate, x1,  x2,  x3,  x4,  x5,  x6) as (      
    select date '2018-01-01', 2 ,  1 ,  6 ,  2 ,  2 ,  1 from dual union all
    select date '2018-01-02', 2 ,  2 ,  3 ,  3 ,  2 ,  3 from dual union all
    select date '2018-01-03', 5 ,  5 ,  3 ,  2 ,  3 ,  2 from dual union all
    select date '2018-01-04', 5 ,  4 ,  4 ,  4 ,  5 ,  5 from dual union all
    select date '2018-01-05', 4 ,  3 ,  4 ,  1 ,  2 ,  6 from dual union all
    select date '2018-01-06', 1 ,  2 ,  2 ,  5 ,  2 ,  2 from dual
)

you can use a MERGE and an analytic function for the ordering:

merge into tabTest t
using (select rowid, row_number() over (order by createDate) as RN from tabTest) t2
on (t2.RN = 4 and t.rowid = t2.rowid)
when matched then
    update set x4 = 0

The same way, you can order rows based on whatever column or combination of columns, but you really need an ordering criteria to say which one is the 4th row.

How to make update from that select statement PL/SQL

3 Answers3