p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: Re: General SQL question....


Message #1 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 3 Jun 2002 15:11:26 -0400
Fascinating!  Nevermind then. :)  It was my understanding that the FROM
clause could only be used in a SELECT statement, and not in an UPDATE.  I
guess my documentation is not correct.

Regards,
Pete


> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: Monday, June 03, 2002 3:02 PM
> To: ASP Databases
> Subject: [asp_databases] Re: General SQL question....
> 
> 
> Um. Yes.
> 
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...] 
> Sent: Monday, June 03, 2002 12:04 PM
> To: ASP Databases
> Subject: [asp_databases] Re: General SQL question....
> 
> 
> And did it update the field as you expected?  
> 
> 
> > -----Original Message-----
> > From: Owen Mortensen [mailto:ojm@a...]
> > Sent: Monday, June 03, 2002 2:56 PM
> > To: ASP Databases
> > Subject: [asp_databases] Re: General SQL question....
> > 
> > 
> > Hmmm.  I tested this syntax (with the FROM statement) on a
> > test database
> > and it seemed to work fine.  Here's the syntax:
> > 
> > UPDATE prj_item
> > SET sequence = sequence + 1
> > FROM prj_item_kpt itk INNER JOIN
> >     prj_item itm ON itk.item_id = itm.id AND itk.kpt_id = 3 WHERE 
> > itm.sequence > 27
> > 
> > Owen
> > 
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Monday, June 03, 2002 11:18 AM
> > To: ASP Databases
> > Subject: [asp_databases] Re: General SQL question....
> > 
> > 
> > No, your SQL statement is not correct.  The syntax for the UPDATE 
> > statement is this:
> > 
> > UPDATE table
> > SET newvalue
> > WHERE criteria;
> > 
> > You can NOT include the FROM statement.  Try this instead:
> > 
> > UPDATE Table01 INNER JOIN Table02 ON Table01.ID = 
> Table02.ID LEFT JOIN
> 
> > Table03 ON Table02.ID = Table03.ID SET FieldName = 'new 
> value' WHERE 
> > Table02.RefVal = 16
> > 
> > That should do it.  Note, it is a bad idea to name fields
> > "ID".  You may
> > run into problems because of that.  You should rename those 
> fields to
> > something more specific to the table (t1ID, t2ID, t3ID... 
> or something
> > like that).
> > 
> > Regards,
> > Peter
> > 
> > 
> > > -----Original Message-----
> > > From: Sandra [mailto:salau@p...]
> > > Sent: Monday, June 03, 2002 7:07 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Re: General SQL question....
> > > 
> > > 
> > > Hi Owen,
> > > 
> > > 
> > 
> ---------------------------------------------------------------------
> > > Say I have several tables in a database and I get a recordset 
> > > through a couple of inner and outer joins.  Is that recordset
> > > updateable (assuming
> > > I did not open it read-only, etc.)?  Also, would it be 
> > possible to do
> > > some kind of "UPDATE" statement using several joined tables?
> > > Something
> > > like:
> > >  
> > > UPDATE Table01
> > > SET FieldName = 'new value'
> > > FROM Table01 T1 INNER JOIN Table02 T2 on T1.ID = T2.ID LEFT
> > OUTER JOIN
> > 
> > > Table03 T3 on T2.ID=T3.ID WHERE T2.RefVal = 16
> > > 
> > 
> ---------------------------------------------------------------------
> > > 
> > > Your SQL statement is correct. However, when I attempted the same 
> > > thing for my own application, it won't allow me to update 
> through a
> > > statement 
> > > that references more than one table. What you might want to 
> > > do instead is 
> > > open a recordset per table and update that way. It may seem 
> > a little
> > > tedious, but VBScript won't allow for updates on joined
> > > tables, it seems.
> > > 
> > > Unless if someone else can prove me wrong, this is from my own 
> > > experience. :)
> > > 
> > > Sandra
> > > 
> > 
> > 
> > 
> > 
> > 
> 
> 
> 
> 
> 
Message #2 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 3 Jun 2002 15:04:05 -0400
And did it update the field as you expected?  


> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: Monday, June 03, 2002 2:56 PM
> To: ASP Databases
> Subject: [asp_databases] Re: General SQL question....
> 
> 
> Hmmm.  I tested this syntax (with the FROM statement) on a 
> test database
> and it seemed to work fine.  Here's the syntax:
> 
> UPDATE prj_item
> SET sequence = sequence + 1
> FROM prj_item_kpt itk INNER JOIN
>     prj_item itm ON itk.item_id = itm.id AND itk.kpt_id = 3
> WHERE itm.sequence > 27
> 
> Owen
> 
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...] 
> Sent: Monday, June 03, 2002 11:18 AM
> To: ASP Databases
> Subject: [asp_databases] Re: General SQL question....
> 
> 
> No, your SQL statement is not correct.  The syntax for the UPDATE
> statement is this:
> 
> UPDATE table
> SET newvalue
> WHERE criteria;
> 
> You can NOT include the FROM statement.  Try this instead:
> 
> UPDATE Table01 INNER JOIN Table02 ON Table01.ID = Table02.ID LEFT JOIN
> Table03 ON Table02.ID = Table03.ID SET FieldName = 'new value' WHERE
> Table02.RefVal = 16
> 
> That should do it.  Note, it is a bad idea to name fields 
> "ID".  You may
> run into problems because of that.  You should rename those fields to
> something more specific to the table (t1ID, t2ID, t3ID... or something
> like that).
> 
> Regards,
> Peter
> 
> 
> > -----Original Message-----
> > From: Sandra [mailto:salau@p...]
> > Sent: Monday, June 03, 2002 7:07 PM
> > To: ASP Databases
> > Subject: [asp_databases] Re: General SQL question....
> > 
> > 
> > Hi Owen,
> > 
> > 
> ---------------------------------------------------------------------
> > Say I have several tables in a database and I get a recordset
> > through a
> > couple of inner and outer joins.  Is that recordset 
> > updateable (assuming
> > I did not open it read-only, etc.)?  Also, would it be 
> possible to do
> > some kind of "UPDATE" statement using several joined tables?  
> > Something
> > like:
> >  
> > UPDATE Table01
> > SET FieldName = 'new value'
> > FROM Table01 T1 INNER JOIN Table02 T2 on T1.ID = T2.ID LEFT 
> OUTER JOIN
> 
> > Table03 T3 on T2.ID=T3.ID WHERE T2.RefVal = 16
> > 
> ---------------------------------------------------------------------
> > 
> > Your SQL statement is correct. However, when I attempted the
> > same thing 
> > for my own application, it won't allow me to update through a 
> > statement 
> > that references more than one table. What you might want to 
> > do instead is 
> > open a recordset per table and update that way. It may seem 
> a little 
> > tedious, but VBScript won't allow for updates on joined 
> > tables, it seems.
> > 
> > Unless if someone else can prove me wrong, this is from my own
> > experience. :)
> > 
> > Sandra
> > 
> 
> 
> 
> 
> 
Message #3 by "Owen Mortensen" <ojm@a...> on Mon, 3 Jun 2002 12:01:43 -0700
Um. Yes.

-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...] 
Sent: Monday, June 03, 2002 12:04 PM
To: ASP Databases
Subject: [asp_databases] Re: General SQL question....


And did it update the field as you expected?  


> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: Monday, June 03, 2002 2:56 PM
> To: ASP Databases
> Subject: [asp_databases] Re: General SQL question....
> 
> 
> Hmmm.  I tested this syntax (with the FROM statement) on a
> test database
> and it seemed to work fine.  Here's the syntax:
> 
> UPDATE prj_item
> SET sequence = sequence + 1
> FROM prj_item_kpt itk INNER JOIN
>     prj_item itm ON itk.item_id = itm.id AND itk.kpt_id = 3 WHERE 
> itm.sequence > 27
> 
> Owen
> 
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, June 03, 2002 11:18 AM
> To: ASP Databases
> Subject: [asp_databases] Re: General SQL question....
> 
> 
> No, your SQL statement is not correct.  The syntax for the UPDATE 
> statement is this:
> 
> UPDATE table
> SET newvalue
> WHERE criteria;
> 
> You can NOT include the FROM statement.  Try this instead:
> 
> UPDATE Table01 INNER JOIN Table02 ON Table01.ID = Table02.ID LEFT JOIN

> Table03 ON Table02.ID = Table03.ID SET FieldName = 'new value' WHERE 
> Table02.RefVal = 16
> 
> That should do it.  Note, it is a bad idea to name fields
> "ID".  You may
> run into problems because of that.  You should rename those fields to
> something more specific to the table (t1ID, t2ID, t3ID... or something
> like that).
> 
> Regards,
> Peter
> 
> 
> > -----Original Message-----
> > From: Sandra [mailto:salau@p...]
> > Sent: Monday, June 03, 2002 7:07 PM
> > To: ASP Databases
> > Subject: [asp_databases] Re: General SQL question....
> > 
> > 
> > Hi Owen,
> > 
> > 
> ---------------------------------------------------------------------
> > Say I have several tables in a database and I get a recordset 
> > through a couple of inner and outer joins.  Is that recordset
> > updateable (assuming
> > I did not open it read-only, etc.)?  Also, would it be 
> possible to do
> > some kind of "UPDATE" statement using several joined tables?
> > Something
> > like:
> >  
> > UPDATE Table01
> > SET FieldName = 'new value'
> > FROM Table01 T1 INNER JOIN Table02 T2 on T1.ID = T2.ID LEFT
> OUTER JOIN
> 
> > Table03 T3 on T2.ID=T3.ID WHERE T2.RefVal = 16
> > 
> ---------------------------------------------------------------------
> > 
> > Your SQL statement is correct. However, when I attempted the same 
> > thing for my own application, it won't allow me to update through a
> > statement 
> > that references more than one table. What you might want to 
> > do instead is 
> > open a recordset per table and update that way. It may seem 
> a little
> > tedious, but VBScript won't allow for updates on joined
> > tables, it seems.
> > 
> > Unless if someone else can prove me wrong, this is from my own 
> > experience. :)
> > 
> > Sandra
> > 
> 
> 
> 
> 
> 



Message #4 by "the Office of Brent Allen VanderMeide" <ccbbttmm@a...> on Mon, 3 Jun 2002 12:25:35 -0600
Hello Owen,

The proper and best way to accomplish your task is to setup a table
view.  I'm not sure if you're familiar with them, but one of it's main
purposes is for this.

A view allows you to access for read/write to multiple tables and
columns at the same time.  A view is just a select statement stored in
SQL Server to create a virtual table(which means you can SELECT, INSERT,
and UPDATE like a normal table).

However, the view MUST contain the required columns for each table it is
joined to for INSERT'ing new records.


MyView Code in SQL
----------------------------------
SELECT Table01.FieldName, Table02.RefVal
FROM Table01
INNER JOIN Table02 on Table01.ID = Table02.ID


Update Statement
----------------------------------
UPDATE MyView
SET FieldName = 'new value'
WHERE MyView.RefVal = 16


Thank you for the opportunity to help.  I hope this has not only helped
you but others who have dealt with trying to Update multiple tables with
one statement.


Thank you,

Brent Allen VanderMeide
Intermountain Software Solutions
Owner / Senior Web & Application Developer
support@s... 


Message #5 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 3 Jun 2002 14:18:11 -0400
No, your SQL statement is not correct.  The syntax for the UPDATE statement
is this:

UPDATE table
SET newvalue
WHERE criteria;

You can NOT include the FROM statement.  Try this instead:

UPDATE Table01 INNER JOIN Table02 ON Table01.ID = Table02.ID LEFT JOIN
Table03 ON Table02.ID = Table03.ID
SET FieldName = 'new value'
WHERE Table02.RefVal = 16

That should do it.  Note, it is a bad idea to name fields "ID".  You may run
into problems because of that.  You should rename those fields to something
more specific to the table (t1ID, t2ID, t3ID... or something like that).

Regards,
Peter


> -----Original Message-----
> From: Sandra [mailto:salau@p...]
> Sent: Monday, June 03, 2002 7:07 PM
> To: ASP Databases
> Subject: [asp_databases] Re: General SQL question....
> 
> 
> Hi Owen, 
> 
> ---------------------------------------------------------------------
> Say I have several tables in a database and I get a recordset 
> through a
> couple of inner and outer joins.  Is that recordset 
> updateable (assuming
> I did not open it read-only, etc.)?  Also, would it be possible to do
> some kind of "UPDATE" statement using several joined tables?  
> Something
> like:
>  
> UPDATE Table01
> SET FieldName = 'new value'
> FROM Table01 T1 INNER JOIN Table02 T2 on T1.ID = T2.ID LEFT OUTER JOIN
> Table03 T3 on T2.ID=T3.ID
> WHERE T2.RefVal = 16
> ---------------------------------------------------------------------
> 
> Your SQL statement is correct. However, when I attempted the 
> same thing 
> for my own application, it won't allow me to update through a 
> statement 
> that references more than one table. What you might want to 
> do instead is 
> open a recordset per table and update that way. It may seem a little 
> tedious, but VBScript won't allow for updates on joined 
> tables, it seems.
> 
> Unless if someone else can prove me wrong, this is from my own 
> experience. :)
> 
> Sandra  
> 
Message #6 by "Sandra" <salau@p...> on Mon, 3 Jun 2002 19:06:47
Hi Owen, 

---------------------------------------------------------------------
Say I have several tables in a database and I get a recordset through a
couple of inner and outer joins.  Is that recordset updateable (assuming
I did not open it read-only, etc.)?  Also, would it be possible to do
some kind of "UPDATE" statement using several joined tables?  Something
like:
 
UPDATE Table01
SET FieldName = 'new value'
FROM Table01 T1 INNER JOIN Table02 T2 on T1.ID = T2.ID LEFT OUTER JOIN
Table03 T3 on T2.ID=T3.ID
WHERE T2.RefVal = 16
---------------------------------------------------------------------

Your SQL statement is correct. However, when I attempted the same thing 
for my own application, it won't allow me to update through a statement 
that references more than one table. What you might want to do instead is 
open a recordset per table and update that way. It may seem a little 
tedious, but VBScript won't allow for updates on joined tables, it seems.

Unless if someone else can prove me wrong, this is from my own 
experience. :)

Sandra  
Message #7 by "Owen Mortensen" <ojm@a...> on Mon, 3 Jun 2002 11:55:56 -0700
Hmmm.  I tested this syntax (with the FROM statement) on a test database
and it seemed to work fine.  Here's the syntax:

UPDATE prj_item
SET sequence = sequence + 1
FROM prj_item_kpt itk INNER JOIN
    prj_item itm ON itk.item_id = itm.id AND itk.kpt_id = 3
WHERE itm.sequence > 27

Owen

-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...] 
Sent: Monday, June 03, 2002 11:18 AM
To: ASP Databases
Subject: [asp_databases] Re: General SQL question....


No, your SQL statement is not correct.  The syntax for the UPDATE
statement is this:

UPDATE table
SET newvalue
WHERE criteria;

You can NOT include the FROM statement.  Try this instead:

UPDATE Table01 INNER JOIN Table02 ON Table01.ID = Table02.ID LEFT JOIN
Table03 ON Table02.ID = Table03.ID SET FieldName = 'new value' WHERE
Table02.RefVal = 16

That should do it.  Note, it is a bad idea to name fields "ID".  You may
run into problems because of that.  You should rename those fields to
something more specific to the table (t1ID, t2ID, t3ID... or something
like that).

Regards,
Peter


> -----Original Message-----
> From: Sandra [mailto:salau@p...]
> Sent: Monday, June 03, 2002 7:07 PM
> To: ASP Databases
> Subject: [asp_databases] Re: General SQL question....
> 
> 
> Hi Owen,
> 
> ---------------------------------------------------------------------
> Say I have several tables in a database and I get a recordset
> through a
> couple of inner and outer joins.  Is that recordset 
> updateable (assuming
> I did not open it read-only, etc.)?  Also, would it be possible to do
> some kind of "UPDATE" statement using several joined tables?  
> Something
> like:
>  
> UPDATE Table01
> SET FieldName = 'new value'
> FROM Table01 T1 INNER JOIN Table02 T2 on T1.ID = T2.ID LEFT OUTER JOIN

> Table03 T3 on T2.ID=T3.ID WHERE T2.RefVal = 16
> ---------------------------------------------------------------------
> 
> Your SQL statement is correct. However, when I attempted the
> same thing 
> for my own application, it won't allow me to update through a 
> statement 
> that references more than one table. What you might want to 
> do instead is 
> open a recordset per table and update that way. It may seem a little 
> tedious, but VBScript won't allow for updates on joined 
> tables, it seems.
> 
> Unless if someone else can prove me wrong, this is from my own
> experience. :)
> 
> Sandra
> 




  Return to Index