Update any existing row

Question

I am trying to update all rows in my CASE table:

UPDATE CASE c
SET c.number =
(
 select p.number 
 from CASE c, Papers p, Dokument d
 where c.dokument = d.id
 and p.id = d.paperid
 and p.numer is not null
 and c.case = null;
)
where 
(this same as in set : when case.number is null but papers.numer is not null)

I can't do this because I don't know how to write where clauses. It must update all rows where case.numer is null and papers.number is not null.

How can I write this? Could I write some loop? I've never used PL/SQL so I don't know how to do that either.

I've also tried to update like this but got an error:

UPDATE s
SET s.number = w.number
FROM CASE s
JOIN Dokument d on d.CASEID =s.ID
JOIN Paper w on w.DOKUMENTID =d.ID and w.number is NOT NULL 
WHERE s.number IS NULL 
AND s.secondNumber IS NULL
AND s.FIRSTNAME = w.FIRSTNAME
AND s.SURNAME = w.SURNAME;

SQL Error: ORA-00933: polecenie SQL niepoprawnie zakończone
00933. 00000 -  "SQL command not properly ended"

Based on the comment in the last sentence, I removed the Oracle tag. — Gordon Linoff, Mar 31 '16 at 14:08
Please be clear which DBMS you are using. You've now added the PL/SQL tag, which would be appropriate for Oracle, not SQL Server. These things matter. — Alex Poole, Apr 01 '16 at 08:46

score 3 · Answer 1 · answered Mar 31 '16 at 14:10

3

This is the way how I would do it in T-SQL ....

update c
set c.number = p.number
from [CASE] c
join [Dokument] d on c.dokument = d.id
join [Papers] p on p.id = d.paperid and p.number is not null
where c.number is null

But as I can't see your table structure, I guessed for some reason your table name is [Case], bad practice if i guessed it right ...

Give it a try and let me know if it works

answered Mar 31 '16 at 14:10

Veljko89

1,813
3
28
43

Thank you, can you tell if it update all row, or only first row , that meets this conditions? – jan345 Mar 31 '16 at 14:21
It updates all row that meet the where condition ... in this case all rows that have number null in [Case] table ... but test it out – Veljko89 Mar 31 '16 at 14:22
Thank you I'm out of my working computer, but tomorrow i tell you if it all works – jan345 Mar 31 '16 at 14:24
It's not working i change it because i must write it in PL/SQL and and conditions i add it in orginal post – jan345 Apr 01 '16 at 07:28
Don't forget to change tag from T-SQL to PL/SQL ... that's major info for every user of SO out there – Veljko89 Apr 01 '16 at 07:35

Alex Poole · Answer 2 · 2016-04-01T08:53:37.467

Your second statement, added in an edit, isn't valid for Oracle as you can't join in an update (or delete) statement. So you need a subquery, as you attempted in your first statement.

But in that subquery attempt, you aren't correlating properly; the reference to the CASE table in the subquery shouldn't be there, as you can refer to the alias from the update clause. And you can then do the same query in an exists clause to decide what to update:

update case c
set c.number =
(
 select p.number 
 from papers p
 join dokument d
 on d.paperid = p.id
 where c.dokument = d.id
 and p.number is not null
)
where c.number is null
and exists (
 select p.number 
 from papers p
 join dokument d
 on d.paperid = p.id
 where c.dokument = d.id
 and p.number is not null
);

This assumes there will only be (at most) one matching p.number, which may not be the case. If there can be more than one then you need a way to pick which to use - with max/min, some ordering criteria, etc. Using an aggregate would make the p.number is not null check a bit redundant.

('number' isn't a valid column name; you've used 'number' and 'numer' in the question; substitute your actual column name. 'case' also isn't a good name for a table as that's a function name).

Update any existing row

2 Answers2