update table column from another table

elygp · December 28th, 2008, 09:37 PM

Hi,

I have the scenario below which I hope you could help me:

I have two tables:

TABLE_X:

xid xname
A
B
C

TABLE_Y:

yid | yname
A | A_NAME
B | B_NAME1
B | B_NAME2
C | C_NAME
C | C_NAME

I want to update the xname column witht he value of yname column, but all xid with different yname from table_y should have the value 'DUPLICATE'

TABLE_X:
xid | xname
A | A_NAME
B | DUPLICATE
C | C_NAME

As shown above, xid=B got xname=DUPLICATE because there are two yid=B in the table_Y with different yname. Thank you.

Old Pedant · December 28th, 2008, 09:40 PM

I can't understand your tables because you didn't put [ code ] ... [ /code ] tags around them, so the fields all run together.

Also, I'd MUCH rather see the two tables COMPLETELY separate, not munged together.

And, finally: What database??? Yes, it matters.

Old Pedant · December 28th, 2008, 09:44 PM

The way I read your post, you have these existing tables:

Code:

TableX
xid :: xname
A   :: [null] 
B   :: [null] 
C   :: [null]

TableY
yid :: yname
A   :: A_NAME
B   :: B_Name
B   :: B_NAME2
C   :: C_NAME

Yes??? No???

And what you WANT from that is

Code:

TableX
xid :: xname
A   :: A_NAME
B   :: DUPLICATE
C   :: C_NAME

with TableY left unchanged.

Yes?? No???

If yes, we still need to know what DB, because multi-table UPDATEs are different in different DBs.

Also...what should be done if some xname *already* has a value???

elygp · December 28th, 2008, 10:02 PM

Quote:

Originally Posted by Old Pedant

The way I read your post, you have these existing tables:

Code:

TableX
xid :: xname
A   :: [null] 
B   :: [null] 
C   :: [null]
 
TableY
yid :: yname
A   :: A_NAME
B   :: B_Name
B   :: B_NAME2
C   :: C_NAME

Yes??? No???

And what you WANT from that is

Code:

TableX
xid :: xname
A   :: A_NAME
B   :: DUPLICATE
C   :: C_NAME

with TableY left unchanged.

Yes?? No???

If yes, we still need to know what DB, because multi-table UPDATEs are different in different DBs.

Also...what should be done if some xname *already* has a value???

Thanks Old Pedant for the reply. I forgot to mention, only those xname without a value (NULL or space) should be updated. Table Y will be left unchanged. Im using Sybase database. thank you so much!

Old Pedant · December 28th, 2008, 11:21 PM

Ummm...you didn't answer either of my questions. Yes or No to both???

I *ASSUME* Yes to both, since you didn't object.

Unfortunately, I do *NOT* know the syntax of Sybase SQL. But since Microsoft's SQL Server is actually derived (more than 10 years ago) from Sybase, I will *GUESS* that it is the same. If not, you'll have to ask for some help in a Sybase forum, most likely.

Back later. Maybe tomorrow.

elygp · December 28th, 2008, 11:30 PM

THanks anyway, but I think I got the code i need, I used "group by" clause to select the distinct xid, xname.

Old Pedant · December 29th, 2008, 02:23 AM

Not sure how that's enough. Seems to me you need to do a check on the count, as in

Code:

    SELECT tableY.yid, MAX(tableY.yname) AS theNameToUse, COUNT(DISTINCT tableY.yname)
    FROM tableY
    GROUP BY tableY.yid
    HAVING COUNT(DISTINCT tableY.yname) = 1

or something along those lines.