Help me convert a SELECT statement to an UPDATE statement

Help me convert a SELECT statement to an UPDATE statement

Post by Richard More » Wed, 26 Mar 2003 02:52:36



Hi,

I am trying to update a field in a table with the value in a field from
another table.. This is the SELECT statment I've run to see the two values
side by side:

SELECT     a.RESKEY, a.ROOMNUMB, a.CCFEE, b.DCARD
FROM         webTableReservations a INNER JOIN
                      seattdata b ON b.DBEGIN = a.ARRIVE AND b.DROOM =
a.ROOMNUMB
WHERE     (a.CCFEE IS NULL)

What I am try to do is this..

UPDATE webTableReservations as a
SET a.CCFEE = b.DCARD
WHERE b.DBEGIN = a.ARRIVE AND b.DROOM = a.ROOMNUMB

Execept that when I try to run this SQL I get an "Incorrect Syntax Near
Keyword AS"

I've tried re-working the SQL statment a few different ways, but can't get
it to execute..

Thanks,

Rich

 
 
 

Help me convert a SELECT statement to an UPDATE statement

Post by Anith Se » Wed, 26 Mar 2003 04:02:23


Do:

UPDATE webTableReservations
   SET CCFEE = (SELECT DCARD
                  FROM seattdata
                 WHERE seattdata.DBEGIN  = webTableReservations.ARRIVE
                   AND seattdata.DROOM = webTableReservations.ROOMNUMB)
 WHERE EXISTS  (SELECT *
                  FROM seattdata
                 WHERE seattdata.DBEGIN  = webTableReservations.ARRIVE
                   AND seattdata.DROOM = webTableReservations.ROOMNUMB)
   AND CCFEE IS NULL

In T-SQL you can also re-write this as:

UPDATE webTableReservations
   SET CCFEE = seattdata.DCARD
 WHERE seattdata.DBEGIN  = webTableReservations.ARRIVE
   AND seattdata.DROOM = webTableReservations.ROOMNUMB
   AND CCFEE IS NULL

--
- Anith
(Please respond only to newsgroups)

 
 
 

Help me convert a SELECT statement to an UPDATE statement

Post by Anith Se » Wed, 26 Mar 2003 09:30:46


I made a mistake in the T-SQL version (#2), it should be:

UPDATE webTableReservations
   SET CCFEE = seattdata.DCARD
  FROM seattdata
 WHERE seattdata.DBEGIN  = webTableReservations.ARRIVE
   AND seattdata.DROOM = webTableReservations.ROOMNUMB
   AND CCFEE IS NULL

--
- Anith
(Please respond only to newsgroups)

 
 
 

Help me convert a SELECT statement to an UPDATE statement

Post by Richard More » Wed, 26 Mar 2003 23:13:17


Hi,

Thanks.. this seems to have worked..

Rich


Quote:> I made a mistake in the T-SQL version (#2), it should be:

> UPDATE webTableReservations
>    SET CCFEE = seattdata.DCARD
>   FROM seattdata
>  WHERE seattdata.DBEGIN  = webTableReservations.ARRIVE
>    AND seattdata.DROOM = webTableReservations.ROOMNUMB
>    AND CCFEE IS NULL

> --
> - Anith
> (Please respond only to newsgroups)

 
 
 

1. SELECT statements converted to SELECT FOR UPDATE

We've got some deadlock issues that we're trying to solve. In looking
at the logs we note a bunch of statements that were written as SELECT
being listed by PG as SELECT FOR UPDATE. Was wondering what leads to
that (foreign keys, transactions). We are using JDBC and are not tried
setting the transaction isolation level of the connection.

Any thoughts appreciated.

2. pgsql/src/backend/rewrite (rewriteHandler.c rewriteManip.c)

3. convert select statement ro delete statement

4. Authorization Question

5. USING A FIELD FROM MAIN SELECT STATEMENT IN SUB SELECT STATEMENT

6. Need help conecting to neworked databases via internet.

7. help!!! SELECT and Update sql statement

8. how to remove a database?

9. insert statement using values from a select statement

10. IF STATEMENTS IN SELECT STATEMENTS

11. Running SELECT statement within EXECUTE statement

12. Sleeping SELECT statement is blocking another statement

13. Conditional Statements in the INSERT-SELECT statement!