Update a table using a join??? (Syntax Question)

Update a table using a join??? (Syntax Question)

Post by SFRATTUR » Fri, 30 Mar 2001 04:44:26



I have two tables:

Member Table  has two fields  (MemberID, LastLogin)....Nulls are not
permitted

State Table has two fields (MemberID, LastCalledDate)...Nulls are not
permitted

____________________________________________________________________________
_________
I want to something like this, bu t I cant get the Syntax right

        Update Member set LastLogin = (select LastCalledDate from Stats
where Member.MemberID = Stats.MemberID)
____________________________________________________________________________
_________

Whats wrong with my syntax here?

Thanks

Sandro Frattura

 
 
 

Update a table using a join??? (Syntax Question)

Post by Carlos Eduardo Roja » Fri, 30 Mar 2001 05:05:38


Try this:
UPDATE Member
SET lastlogin = lastcalleddate
FROM Member JOIN Stats
ON Member.memberid = Stats.memberid
--
Hope this helps,,
---------------------------------------------------------------------
Carlos Eduardo Rojas
MCSE+I, MCDBA, MCSS, SQL Server MVP
Pass Spanish Group


Quote:> I have two tables:

> Member Table  has two fields  (MemberID, LastLogin)....Nulls are not
> permitted

> State Table has two fields (MemberID, LastCalledDate)...Nulls are not
> permitted

____________________________________________________________________________
Quote:> _________
> I want to something like this, bu t I cant get the Syntax right

>         Update Member set LastLogin = (select LastCalledDate from Stats
> where Member.MemberID = Stats.MemberID)

____________________________________________________________________________
Quote:> _________

> Whats wrong with my syntax here?

> Thanks

> Sandro Frattura


 
 
 

Update a table using a join??? (Syntax Question)

Post by Joe Celk » Fri, 30 Mar 2001 05:21:26


Please post DDL, so that people do not have to guess what the keys, NULL-ability of the columns, CHECK() constraints, Declarative Referential Integrity, datatypes, etc. in your schema.  

Tables have columns and not fields; there is a MAJOR diffrence in the two.

Here is my guess as to what you meant to say:

CREATE TABLE Members
(member_id INTEGER NOT NULL PRIMARY KEY,
 last_login DATETIME NOT NULL);

Notice that the table could have been declared this way:

CREATE TABLE Members
(member_id INTEGER NOT NULL,
 last_login DATETIME NOT NULL,
 PRIMARY KEY (member_id, last_login));

This is why you need to post DDL.

CREATE TABLE States
(member_id INTEGER NOT NULL PRIMARY KEY,
 last_call_date DATETIME NOT NULL);

Or did you mean this?

CREATE TABLE Stats
(member_id INTEGER NOT NULL,
 last_call_date DATETIME NOT NULL,
 PRIMARY KEY (member_id, last_call_date));

What table does the member_id reference in each case?

UPDATE Members
   SET last_login
        = (SELECT last_called_date
             FROM Stats AS S1
            WHERE Members.member_id = S1.member_id);

The syntax is fine.  BUT if member_id is not in a 1:1 relationship between both tables, but you can get cardinality violations, generate NULLs if there is no match between the tables, etc.

Without the DDL, your question is impossible to answer

--CELKO--

SQL guru at Trilogy
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Question:Updates to joined tables using cursor in v6.0

Does anybody know how to update joined tables using a cursor where a table is
joined to itself?  According to the documentation on cursors, the table which
is named in the update statement is the one which gets modified.  However, if
a table is joined to itself, how do you specify which one gets updated?
I have tried using an alias to identify the table but the UPDATE statement
doesn't recognize it.

Any information would be appreciated.

2. Create unique index in paradox

3. Bug in UPDATE using ANSI JOIN syntax????????

4. how vb handles Winziped .mdb file with password protected

5. FYI - New syntax allows you to update one table while joining to others

6. DTS checking if file exists?

7. Newbie - Easy (I think) SQL Syntax Question, 3 table join

8. odbc connection loss

9. update using join on multiple tables

10. Update Table using Table Variable Question

11. Update into a table using an outer join

12. Table reference in INSERT/UPDATE stmt using ANSI JOIN

13. Prob with Update Sp using joined tables