SQL Server UPDATING one table from values from another table [duplicate]

Question

I am trying to update values from one table to another in SQL Server. I have 2 tables: tblBus and tblRB_ID.

create table tblBus
(
    bus varchar (10) NOT NULL PRIMARY KEY,
    rb_id int FOREIGN KEY REFERENCES tblRB_ID(rb_id2),
    roadside_bottom varchar (20)
);

Bus	rb_id	roadside_bottom
1000	NULL	NULL
1001	NULL	NULL

Here is tblRB_ID:


create table tblRB_ID
(
    rb_id2 int NOT NULL IDENTITY (1,1) PRIMARY KEY,
    contract_id int FOREIGN KEY REFERENCES tblContracts(contract_id),
    rb_name varchar (20)
    CONSTRAINT rb_pass CHECK
    (
        rb_name IN ('King', 'Super King', 'Kong', 'Half Side')
    )
);

rb_id2	contract_id	roadside_bottom
8	1	King
9	2	Super King

I tried to update the values using this, but I just get an error saying "Incorrect syntax near the keyword 'RIGHT'".

UPDATE tblBus
RIGHT JOIN tblRB_ID
ON tblBus.rb_id = tblRB_ID.rb_id2,
   tblBus.roadside_bottom = tblRB_ID.rb_name;
SET tblBus.rb_id = tblRB_ID.rb_id2,
   tblBus.roadside_bottom = tblRB_ID.rb_name;

I want to tblBus to updated with the information from tblRB_ID. I want both records in tblRB_ID to be into tblBus, and any subsequent record that I put into tblRB_ID. I want it to look like this:

Bus	rb_id	roadside_bottom
1000	8	King
1001	9	Super King

Please help.

Thank you

Have a look at the syntax and the Examples (such as this one) in the documentation, and you'll see your UPDATE is quite malformed. The JOINs are before the SET and you are completely missing a FROM. — Thom A
– Thom A ♦, Commented Sep 17, 2021 at 10:26

Jayasurya Satheesh · Accepted Answer · 2021-09-17 10:37:05Z

2

the syntax you are using is different. the update syntax is like below

UPDATE <Table Alias>
SET
    <Column to be updated> = <Column from which you need to update>
    FROM TableA a
        INNER JOIN TableB B
            ON A.<Column Name> = B.<ColumnName>

So your query should actually be like

UPDATE tblBus
SET tblBus.rb_id = tblRB_ID.rb_id2,
   tblBus.roadside_bottom = tblRB_ID.rb_name
  FROM tblBus
        JOIN tblRB_ID -- Inner join is recommended so that the non-matching rows are not updated with NULL
            ON tblBus.rb_id = tblRB_ID.rb_id2
                AND tblBus.roadside_bottom = tblRB_ID.rb_name;

edited Sep 17, 2021 at 10:37

answered Sep 17, 2021 at 10:28

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

LongLouis Over a year ago

Would this also update the two values that I have and any subsequent records I put in tblRB_ID? I tried using this, but I end up getting "Incorrect syntax near the keyword 'AND'".

Jayasurya Satheesh Over a year ago

@LongLouis there was a comma just before the AND, removed that now

LongLouis Over a year ago

Thank you, but it seems that it's not changing the values on tblBus. I did put INNER join and removed the comma.

Jayasurya Satheesh Over a year ago

Just make sure that the join columns do have matching values.

LongLouis Over a year ago

Oh, thanks for the information. How about if I want to update null values on tblBus from values from tblRB_ID? If not Inner Join, right join?

|

Collectives™ on Stack Overflow

SQL Server UPDATING one table from values from another table [duplicate]

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related