473,735 Members | 3,971 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Update with a Join Syntax

I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.

I need to update a field in the NAMEINFO table for a particular surname in a
particular town.

I can select the records fine with this syntax (testing in Oracle SQL* Plus)

SELECT NAMEINFO.LASTNA ME, NAMEINFO.FIRSTN AME, NAMEINFO.MIDDLE NAME,
NAMEINFO.GENDER , ADDRESSINFO.REG ION FROM NAMEINFO, ADDRESSINFO WHERE
ADDRESSINFO.CIV ICID =NAMEINFO.CIVIC ID (+) AND 'Smith'=NAMEINF O.LASTNAME AND
'Moncton'=ADDRE SSINFO.TOWN;

However, I tried to update the names and failed. Here is my syntax:
UPDATE NAMEINFO SET NAMEINFO.FLAG= 'OK' WHERE ADDRESSINFO.CIV ICID
=NAMEINFO.CIVIC ID (+) AND (('Smith'=NAMEI NFO.LASTNAME) AND
('Moncton'=ADDR ESSINFO.TOWN));
Is there anyway to update the FLAG field with using a Where clause using
bits of 2 tables?
I can do it in Access, using the GUI, but the syntax is different between
the 2 databases...

Help!

Jul 19 '05 #1
7 248474
"Dave" <no****@nospam. com> wrote:
Is there anyway to update the FLAG field with using a
Where clause using bits of 2 tables?


Hi, Dave. I think the syntax would be:

UPDATE NAMEINFO

SET NAMEINFO.FLAG = 'OK'

FROM NAMEINFO [LEFT OUTER(?)] JOIN ADDRESSINFO
ON NAMEINFO.CIVICI D = ADDRESSINFO.CIV ICID

WHERE NAMEINFO.LASTNA ME = 'SMITH'
AND ADDRESSINFO.TOW N = 'MONTCON';

I can't reach an Oracle server from here, so I didn't test this, but it's
very standard SQL (not that that means anything to Oracle). Essentially,
the problem is that you omitted the FROM clause. You can't refer to a table
in a WHERE clause that isn't named in the FROM clause.

There are so many maddening things about Oracle, that I'm not guaranteeing
this will work. It would work on any database grounded in relational
theory, but I looked forward to the chance to work with Oracle for years,
and I've been disappointed to discover that virtually everything about
Oracle is ad-hoc and based on no theory at all. Every day, I waste hours
trying to figure out why entirely ordinary things just don't work in Oracle
('Oh, you need to have some proprietary PRAGMA if you want to do that').

I hope I don't infuriate anyone for whom Oracle is a religion by saying
this. I've spent my career interested in relational theory without respect
to any particular implementation.

A couple of comments. First, JOINS should never be done in the WHERE
clause. That's an archaic construction. They should be done using the
appropriate JOIN syntax; this keeps the distinction between joins and
restricts clear. I wasn't sure whether or why you were trying to do an
outer join, so I put that part of the syntax in brackets. I can't see any
reason for an outer join in the query, but of course, I don't know what
you're trying to do.

Finally, restrict conditions should always be written as COLUMN = Value, not
the other way 'round. It will work either way, but it isn't logical to
write Value = COLUMN.

I hope this was helpful.

Brian

Jul 19 '05 #2
>> [UPDATE with FROM clause] it's very standard SQL (not that that
means anything to Oracle). <<

Nope! There is no FROM clause in a Standard SQL UPDATE statement; it
would make no sense. Other products (SQL Server, Sybase and Ingres)
also use the UPDATE .. FROM syntax, but with **different** semantics.
So it does not port, or even worse, when you do move it, it trashes
your database. Other programmers cannot read it and maintaining it is
harder.

The correct syntax for a searched update statement is

<update statement> ::=
UPDATE <table name>
SET <set clause list>
[WHERE <search condition>]

<set clause list> ::=
<set clause> [{ , <set clause> }...]

<set clause> ::= <object column> = <update source>

<update source> ::= <value expression> | NULL | DEFAULT

<object column> ::= <column name>

The UPDATE clause simply gives the name of the base table or updatable
view to be changed.

Notice that no correlation name is allowed in the UPDATE clause; this
is to avoid some self-referencing problems that could occur. But it
also follows the data model in Standard SQL. When you give a table
expression a correlation name, it is to act as if a materialized table
with that correlation name has been created in the database. That
table then is dropped at the end of the statement. If you allowed
correlation names in the UPDATE clause, you would be updating the
materialized table, which would then disappear and leave the base
table untouched.

The SET clause is a list of columns to be changed or made; the WHERE
clause tells the statement which rows to use. For this discussion, we
will assume the user doing the update has applicable UPDATE privileges
for each <object column>.

* The WHERE Clause

As mentioned, the most important thing to remember about the WHERE
clause is that it is optional. If there is no WHERE clause, all rows
in the table are changed. This is a common error; if you make it,
immediately execute a ROLLBACK statement.

All rows that test TRUE for the <search condition> are marked as a
subset and not as individual rows. It is also possible that this
subset will be empty. This subset is used to construct a new set of
rows that will be inserted into the table when the subset is deleted
from the table. Note that the empty subset is a valid update that
will fire declarative referential actions and triggers.

* The SET Clause

Each assignment in the <set clause list> is executed in parallel and
each SET clause changes all the qualified rows at once. Or at least
that is the theoretical model. In practice, implementations will
first mark all of the qualified rows in the table in one pass, using
the WHERE clause. If there were no problems, then the SQL engine
makes a copy of each marked row in working storage. Each SET clause
is executed based on the old row image and the results are put in the
new row image. Finally, the old rows are deleted and the new rows are
inserted. If an error occurs during all of this, then system does a
ROLLBACK, the table is left unchanged and the errors are reported.
This parallelism is not like what you find in a traditional
third-generation programming language, so it may be hard to learn.
This feature lets you write a statement that will swap the values in
two columns, thus:

UPDATE MyTable
SET a = b, b = a;

This is not the same thing as

BEGIN ATOMIC
UPDATE MyTable
SET a = b;
UPDATE MyTable
SET b = a;
END;

In the first UPDATE, columns a and b will swap values in each row. In
the second pair of UPDATEs, column a will get all of the values of
column b in each row. In the second UPDATE of the pair, a, which now
has the same value as the original value of b, will be written back
into column b -- no change at all. There are some limits as to what
the value expression can be. The same column cannot appear more than
once in a <set clause list> -- which makes sense, given the parallel
nature of the statement. Since both go into effect at the same time,
you would not know which SET clause to use.

If a subquery expression is used in a <set clause>, and it returns a
single value, the result set is cast to a scalar; if it returns an
empty set, the result set is cast to a NULL; if it returns multiple
rows, a cardinality violation is raised.
JOINS should never be done in the WHERE clause. That's an archaic construction. They should be done using the appropriate JOIN syntax;
this keeps the distinction between joins and restricts clear. <<

A lot of newbies (read: SQL Server by way of ACCESS) think that the ON
clause must hold the join condition and the WHERE clause must hold the
restrictions. This is not true. It can make for some nice code, and
might help a smart optimizer materialize common subexpressions across
mulitple queries, show you what should be in a VIEW, etc. But it is
not required.

.. but it isn't logical to write Value = COLUMN. <<


My Arab and Chinese students used to do that, too!
Jul 19 '05 #3
UPDATE NameInfo
SET NameInfo.flag = 'OK'
WHERE NameInfo.lastna me = 'Smith'
AND EXISTS
(SELECT *
FROM AddressInfo AS A1
WHERE A1.civic_id = NameInfo.civic_ id
AND A1.town = 'Moncton');

Read the other posting about the proper syntax for an UPDATE.
Jul 19 '05 #4
jc*******@earth link.net (--CELKO--) wrote in message news:<18******* *************** ****@posting.go ogle.com>...
[UPDATE with FROM clause] it's very standard SQL (not that that

means anything to Oracle). <<
Notice that no correlation name is allowed in the UPDATE clause; this
is to avoid some self-referencing problems that could occur. But it
also follows the data model in Standard SQL. When you give a table
expression a correlation name, it is to act as if a materialized table
with that correlation name has been created in the database. That
table then is dropped at the end of the statement. If you allowed
correlation names in the UPDATE clause, you would be updating the
materialized table, which would then disappear and leave the base
table untouched.


What do you mean that no correlation name is allowed? You can place a
table name (correlation) label on an update, otherwise how would you
perform co-ordingated subqueries? (Code runs on Oracle 7.0 - 9.2)

update wo_routing a
set bkf_flag = 'N'
where oper_seq < (select max(oper_seq) from wo_routing b
where order_no = a.order_no
and work_center_no = a.work_center_n o
and plant = a.plant)
and plant = F_plant
and order_no = F_order_no;

Maybe I misunderstand the use of the term in this context.

HTH -- Mark D Powell --
Jul 19 '05 #5
>> What do you mean that no correlation name is allowed? You can
place a table name (correlation) label on an update, otherwise how
would you perform co-ordingated subqueries? (Code runs on Oracle 7.0 -
9.2) <<

NOT in Standard SQL; look it up!

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898

It would make no sense. The ANSI/ISO model is that a correlation
creates a temporaty working table with that name and the data of the
table expression on the left hand side of the AS operator. You also
have the option of renaming the columns in the new table.

This means that the working table, not the base table, would be
updated and then disappear.
Jul 19 '05 #6
jc*******@earth link.net (--CELKO--) wrote in message news:<18******* *************** ****@posting.go ogle.com>...
What do you mean that no correlation name is allowed? You can

place a table name (correlation) label on an update, otherwise how
would you perform co-ordingated subqueries? (Code runs on Oracle 7.0 -
9.2) <<

NOT in Standard SQL; look it up!

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898

It would make no sense. The ANSI/ISO model is that a correlation
creates a temporaty working table with that name and the data of the
table expression on the left hand side of the AS operator. You also
have the option of renaming the columns in the new table.

This means that the working table, not the base table, would be
updated and then disappear.


This is probably just a terminology problem, more likely on my part,
on what is meant by correlation. I looked in the MS SQL manual and
they also support coordinated subquery on an update. I am sure this
also works in DB2 so it seems unlikely that the table label "A" in my
example represents any type of correlation as meant by the standard.

-- Mark D Powell --
Jul 19 '05 #7
AK
well, the standard is well known, thank you for explaining it again.
I think the reason we do not follow it is also very simple: the
standard is VERY inconvenient in many simple real life situations...

What do you think?
Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
18180
by: Karaoke Prince | last post by:
Hi There, I have an update statement to update a field of a table (~15,000,000 records). It took me around 3 hours to finish 2 weeks ago. After that no one touched the server and no configuration changed. Until yesterday, I re-ran it again and it took me more than 18hrs and still not yet finished!!! What's wrong with it? I can ran it successfully before. I have tried two times but the result was still the same.
14
2515
by: bolidev | last post by:
I'm new to SQL and can't figure out how to update my table (StoreItemStatus) that contains the current status for items in each store (STORE_KEY, ITEM_KEY, STATUS,...). I get updated status info in a table (I'll call it NewInfo) that has similar fields. NewInfo may contain multiple records for each Store/Item, but I will just use the latest status. I'm not sure how to update StoreItemStatus using each record of NewInfo. Any advice is...
17
5024
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by cust_no, ded_type_cd, chk_no)
6
19167
by: HeadScratcher | last post by:
I am trying to speed up my update statements by removing inner select statements. Example: update orders set shipname = (select contactName from customers where customerid = orders.customerID) I read some articles which said that I should be able to use an inner join on the update statement like the following:
4
2251
by: 001 | last post by:
Hello, The select statement needs only 1 second to complete the query. But the update statement spends 30 minutes. Why? SELECT STATEMENT: declare @IDate smalldatetime select @IDate=col001 from USDay select * from USDay A
4
11336
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET AddressDescription of Entity 456 = AddressDescription of Entity_ID 123 Address1 of Entity 456 = Address1 of Entity_ID 123 City of Entity 456 = City of Entity_ID 123
2
2622
by: Sim Zacks | last post by:
The following query updated all the rows in the AssembliesBatch table, not just where batchID=5. There are 2 rows in the AssembliesBatch table with batch ID of 5 and I wanted to update both of them with their price, based on the data in the from clause. One row has 105 units and the other row has 2006 units. the active price in both rows is 6.6 and the pricedifferential is 0. My expectation is that the first row would be updated to 693...
0
2757
by: svgeorge | last post by:
I want to update several tables using one stored procedure. How can i do this I mean the syntax.etc. declaration etc. I know the basic syntax as below CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS...
7
1517
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a particular surname in a particular town. I can select the records fine with this syntax (testing in Oracle SQL* Plus) SELECT NAMEINFO.LASTNAME, NAMEINFO.FIRSTNAME, NAMEINFO.MIDDLENAME, NAMEINFO.GENDER, ADDRESSINFO.REGION FROM NAMEINFO, ADDRESSINFO...
0
8964
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8786
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9463
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9327
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9251
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9200
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8202
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.