none
Stuck on SQL syntax, an UPDATE with a join RRS feed

  • Question

  • Here is my attempt at this SQL - which fails with a syntax error. I need to make an update to CasesNorm but I need to reference a field in SubjectsNorm. How do I do it?

    USE Smart2

    UPDATE dbo.CasesNorm As C INNER JOIN SubjectsNorm As S

    ON C.SubjectId = S.Id

    SET C.AuditTypeId = 4

    WHERE C.CaseTypeId = 3

    AND S.TypePI = 3

    AND C.ContactMethodId not in (9,11,19)

    AND C.ReferralSourceId <> 3

    AND C.Allegation1 NOT IN ('15A','15B','15C','15D','15E')

    AND C.Allegation2 NOT IN ('15A','15B','15C','15D','15E')

    AND C.Allegation3 NOT IN ('15A','15B','15C','15D','15E')

    Tuesday, December 12, 2017 10:25 PM

Answers

  • UPDATE C
    SET C.AuditTypeId = 4
    FROM dbo.CasesNorm As C INNER JOIN SubjectsNorm As S
    ON C.SubjectId = S.Id
    WHERE C.CaseTypeId = 3
    AND S.TypePI = 3
    AND C.ContactMethodId not in (9,11,19)
    AND C.ReferralSourceId <> 3
    AND C.Allegation1 NOT IN ('15A','15B','15C','15D','15E')
    AND C.Allegation2 NOT IN ('15A','15B','15C','15D','15E')
    AND C.Allegation3 NOT IN ('15A','15B','15C','15D','15E')

    Tuesday, December 12, 2017 10:40 PM

All replies

  • UPDATE C
    SET C.AuditTypeId = 4
    FROM dbo.CasesNorm As C INNER JOIN SubjectsNorm As S
    ON C.SubjectId = S.Id
    WHERE C.CaseTypeId = 3
    AND S.TypePI = 3
    AND C.ContactMethodId not in (9,11,19)
    AND C.ReferralSourceId <> 3
    AND C.Allegation1 NOT IN ('15A','15B','15C','15D','15E')
    AND C.Allegation2 NOT IN ('15A','15B','15C','15D','15E')
    AND C.Allegation3 NOT IN ('15A','15B','15C','15D','15E')

    Tuesday, December 12, 2017 10:40 PM
  • You are using syntax which is good on some other engine. Beware of that even if there is an SQL standard, few engines implements the entire standard, and at the same time they have they their own proprietary extensions. Below is a statement is almost ANSI-compilant:

    UPDATE dbo.CasesNorm
    SET        AuditTypeId= 4
    FROM     dbo.CasesNorm AS C
    WHERE C.CaseTypeId= 3
    AND C.ContactMethodIdnot in(9,11,19)
    AND C.ReferralSourceId<> 3
    AND C.Allegation1 NOT IN('15A','15B','15C','15D','15E')
    AND C.Allegation2 NOT IN('15A','15B','15C','15D','15E')
    AND C.Allegation3 NOT IN('15A','15B','15C','15D','15E')
    AND EXISTS (SELECT *
                            FROM     SubjectsNorm AS S
                            WHERE    C.SubjectId = S.Id
                                AND    S.TypePI= 3)

    To be fully ANSI-compliant, you would need to take out the FROM clause and use the table name as prefix throughout the query as the alias is not available.

    Tuesday, December 12, 2017 10:45 PM
  • I would like you to sit down and think a minute. The result of the join operation is supposed to be a new table, built from the two tables that when in the join. This new table exist only in the scope of the statement in which was created. But the original Sybase attempt at SQL decades ago was based on UNIX filesystems. So it allows ambiguous crap like this. It depends on the order of the tables involved, and becomes nondeterministic as well as violating the basic principles of RDBMS. You should never use it.

    Then on top of that, there's no such thing as a magic generic universal "id" in a properly designed schema. Since SQL is based on logic, the law of identity dictates that this must be the identifier of something in particular.

    We don't like repeated groups, which you seem to have. The qualifier "<something>_type_id" makes no sense. According to ISO in the metadata group, you can have a "<something>_type" or "<something>_id" but not this horrible hybrid. You need to get a book on basic data modeling.

    What we do today in SQL (and have for a few years now, even though Microsoft was late getting to the game) is use a MERGE statement. But if you read the manual or any book on SQL, you'll see that the merge statement has to have a source and a target. The target table has to be a base table and it is updated from the source. We have no idea which of your two tables. Please either role.

    UPDATE dbo.Case_Norms AS C
           INNER JOIN 
           Subjects_Norms AS S
           ON C.subject_id = S.subject_id
    SET C.audit_type = 4
    WHERE C.case_type = 3
    AND S.type_pi = 3
    AND C.contact_method NOT IN (9, 11, 19)
    AND C.referral_source <> 3
    -- repeated group in violation of 1NF??
    AND C.Allegation1 NOT IN ('15A', '15B', '15C', '15D', '15E')
    AND C.Allegation2 NOT IN ('15A', '15B', '15C', '15D', '15E')
    AND C.Allegation3 NOT IN ('15A', '15B', '15C', '15D', '15E');

    Why don't you go to the front of this forum and read the rules about posting? We'd like to see some DDL for these tables.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, December 13, 2017 12:45 AM
  • All good information I'm sure. But I don't have time to think, I have a December 29th deadline to meet.

    If I get time (laughs) I will revisit this thread and educate myself later on. Thanks for the information.


    PS. The repeating groups are not of my doing and one of the objects of my project is to normalize the database. This SQL is part of that effort.
    Wednesday, December 13, 2017 1:23 AM
  •  I will revisit this thread and educate myself later on. 

    You'd be the first
    Wednesday, December 13, 2017 1:54 AM