Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: how to update records with data from another table - MS SQL 2000

    i want to update table2.message based on the criteria of table1.name. for example, all records named John will be updated with 'Msg1' in table 2.message. Im using MS SQL 2000 and below is the scenario.

    table1 columns
    ID
    Name

    table2 columns
    ID
    Message

    Select a.Id, a.name, b.message
    from table1 a, table2 b
    where a.id =b.id

    a.id a.name b.message
    1 John Msg1
    2 Steve Msg2
    3 Scott Msg3
    4 John NULL - update b.message to 'Msg1'
    5 Steve NULL - update b.message to 'Msg2'
    6 Scott NULL - update b.message to 'Msg3'
    7 John NULL - update b.message to 'Msg1'
    8 Steve NULL - update b.message to 'Msg2'

    If i will update the record per name i am using the query below and i am pre-selecting all the existing names.

    update table2 b
    set b.message=(Select top 1 b.message
    from table1 a, table2 b
    where a.id =b.id
    and a.name ='John')
    where b.id in (select a.id
    from table1 a, table2 b
    where a.id = b.id
    and b.message is null
    and a.name ='John')

    do you have suggestion on how to update this in bulk without preselecting all the names?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    UPDATE U
    SET U.message = a.message
    FROM table2 U
    	INNER JOIN table1 a ON
    		U.id = a.id
    WHERE U.message is null
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2011
    Location
    New Delhi India
    Posts
    9

    updation in MS SQL

    Make your query look like the help text that comes with sql Server:

    update titles
    SET ytd_sales = titles.ytd_sales + sales.qty
    from titles, sales
    WHERE titles.title_id = sales.title_id

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by sbglobal79 View Post
    Make your query look like the help text that comes with sql Server:

    update titles
    SET ytd_sales = titles.ytd_sales + sales.qty
    from titles, sales
    WHERE titles.title_id = sales.title_id
    This is old school SQL. The ANSI standard is:
    Code:
    from titles 
      INNER JOIN sales ON
        titles.title_id = sales.title_id
    The FROM now only contains the JOIN criteria and the WHERE only contains the business logic (restrictions).
    Last edited by Wim; 09-21-11 at 09:48.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2011
    Posts
    2
    Hi! thanks for the reply. here's the update stmt i constructed.

    UPDATE u
    SET u.message = t3.t3msg
    FROM table2 u
    INNER JOIN (select a.id t2id, a.name t2name
    from table1 a) as t2 ON U.id = t2.t2id
    INNER JOIN (select b.name t3name, max(c.message) t3msg
    from table1 c, table2 c
    where b.id = c.id
    group by c.message) as t3 on t2.t2name =t3.t3name
    WHERE U.message is null

  6. #6
    Join Date
    Sep 2011
    Posts
    71

    Thumbs up Using subquery ,So easy

    EX1

    UPDATE tblA A
    SET A.Fld1 = ( SELECT B.Fld1
    FROM tblB B
    WHERE A.Fld2 = B.Fld2 )
    EX2
    OR using join clause as

    UPDATE TABLEA
    SET b = TABLEB.b1,
    c = TABLEB.c1,
    d = TABLEB.d1
    FROM TABLEA, TABLEB
    WHERE TABLEA.a = TABLEB.a1
    AND TABLEB.e1 > 40

  7. #7
    Join Date
    Sep 2011
    Posts
    75

    Try this

    Hello,

    Try this

    UPDATE table2
    SET table2 .message = table1.message
    FROM table2
    INNER JOIN table1 ON
    table2.id = table1.id
    WHERE table2 .message is not null and table2.username = 'John'


  8. #8
    Join Date
    Apr 2012
    Posts
    2

    Cool Tip: COLLATE DATABASE_DEFAULT

    I was having trouble inserting data from one table to another using SQL2000 due to type differences.

    The server was responding to my attempts with error message: “Cannot resolve collation conflict for equal to operation“.

    After casting the source data into a reference table to match data types, I used the following query to overcome the error and finsh the task:

    UPDATE u
    SET u.uid = s.uid
    FROM build u
    INNER JOIN build_xref s
    ON u.internal COLLATE DATABASE_DEFAULT = s.internal COLLATE DATABASE_DEFAULT
    WHERE u.uid is null

    Hope it helps...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •