simple update statement

Question

Post reply

simple update statement

Best-330874

SSCrazy

Points: 2350
More actions
February 28, 2007 at 9:55 am

#175284

hi ,
   i need help on this.
i have two tables A and B
In table A there is a field called ID which has values like
A10
A20
and so on.
i want to update Table B with THE values above in two columns ID and Code which should have values like this.Table B has two columns ID and Code
ID                             Code
A                                 10
A                                  20

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Will all of the values have the same format of A10? If so, this will work:

DECLARE @table TABLE (value char(3))

DECLARE @table2 TABLE (id CHAR(1), code CHAR(2))

INSERT INTO @table

SELECT 'A10' UNION ALL

SELECT 'A20'

INSERT INTO @table2

SELECT SUBSTRING(value,1,1) AS ID, SUBSTRING(value,2,2) AS Code

FROM @table

SELECT *

FROM @table2

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

Best-330874

SSCrazy

Points: 2350

February 28, 2007 at 10:19 am

#692357

thanks for the help

pinky SSCommitted Points: 1997 More actions · Answer 3

do you want to insert the records from table a into table b or do a update? should your output be somewhat like this

ID Code

A 10

A 20

here is the insert statement

insert into #tableb (id,code)

select left(id,1),substring(id,2,10) from #tableA

Best-330874 SSCrazy Points: 2350 More actions · Answer 4

i want to write an update and not an insert statement so how do i do that?

pinky SSCommitted Points: 1997 More actions · Answer 5

can you explain as to how your input and output will be. your explanation looks as if you want to insert into table b.

Best-330874 SSCrazy Points: 2350 More actions · Answer 6

i want to update table b which has two columns called id and code

from table a which has one column called id

table a has values like A10,A20 and so on

now table b has two columns id and code which needs to be updated so they have values like as below

table b

id code

A 10

A 20

Best-330874 SSCrazy Points: 2350 More actions · Answer 7

Best-330874

SSCrazy

Points: 2350

February 28, 2007 at 11:20 am

#692372

can somebody help on this please

pinky SSCommitted Points: 1997 More actions · Answer 8

what do you want to update tableb with? can you send a sample output? i dont think i understood your question clearly.

Best-330874 SSCrazy Points: 2350 More actions · Answer 9

i have a table A which has column name as FlagID with datatype bit

i want to update the whole column with a value 1

John Rowan SSC Guru Points: 56440 More actions · Answer 10

Whoa, this is totaly different from what you've previously described. It would be very helpful if you would post your table DDL for both tables as well as sample data before and after the update.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

... and, BEST, you need to identify how table A and table B relate to each other... what would the join columns be?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CodeDigger Mr or Mrs. 500 Points: 506 More actions · Answer 12

UPDATE A SET FlagId = 1;

Unless I am overlooking something, this is basic SQL.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 13

Hi ,

This query might help you....

Update B set Code=substring(a.id,2,len(a.id)) from table a join table b on left(a.id,1)=b.id

Regards ,

Amit Gupta

CodeDigger Mr or Mrs. 500 Points: 506 More actions · Answer 14

The UPDATE statement is only valid for changing existing RECORDS.

To "update" a table, you use UPDATE (records that are already present), INSERT (records that are not yet present) and DELETE (records that are superfluous).

In your case, if table B must contain the "same" records as table A and no others, the simplest solution is a general DELETE followed by pinky's suggestion:

DELETE FROM B;

INSERT INTO B(id,code)

SELECT LEFT(id,1),SUBSTRING(id,2,10) FROM A;

If you want to add the new records that do not yet exist in B, leave out the DELETE and INSERT only the new ones:

INSERT INTO B(id,code)

( SELECT LEFT(id,1),SUBSTRING(id,2,10)FROM A

EXCEPT

SELECT id, code FROM B );

If you want to delete the records from B that do not exist in A:

DELETE FROM B

WHERE NOT EXISTS

( SELECT * FROM A

WHERE A.id = B.id + B.code );