Simple Update Query from SQL Newbie

Question

Post reply

Simple Update Query from SQL Newbie

gcook 48147

SSC Veteran

Points: 222
More actions
November 14, 2014 at 11:43 am

#295103

I have a simple table called ADDRESS.
AddressID..........Line1............................Line2
1234.................123 Main St...................Suite 2000
6596.................2400 Peach Tree Lane.....Suite 43
2876.................12 Silver Rd...................Apartment 23
I want to UPDATE the Line 1 column to include the Line2 column where line2 has a suite number but not update records with apartments.
Doing a SELECT statement, I get the result set I want in the Line1 column.
SELECT CONCAT (Line1, ', ',Line2)
FROM address
WHERE Line2 LIKE 'Suite%'
Result: 123 Main St, Suite 2000 (etc.)
Trying to do the update however, I can only manage to update one column at a time instead of all columns where line 2 contains 'Suite.' This of course defeats the purpose of doing this with T-SQL instead of the application GUI.
UPDATE address
SET Line1 = (SELECT CONCAT (Line1,', ',Line2) FROM address WHERE AddressID = '1234' AND Line2 LIKE 'Suite%)
WHERE AddressID = '1234'
How do I join the Line2 column on the end of the Line1 column with a ', ' between on all records with a Line2 column that contains 'Suite xxx'?
Thanks,
-Gary

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

looks like you posted before you did anything more than pasting some sample data.

what is the question?

since your new, i've reformatted your paste into consumable format...then anyone can grab your data and offere a syntax correct, tested solution:

CREATE TABLE #MySampleData(AddressID int, Line1 varchar(100), Line2 varchar(100) )

INSERT INTO #MySampleData

SELECT '1234','123 Main St.','Suite 2000' UNION ALL

SELECT '6596','2400 Peach Tree Lane','Suite 43' UNION ALL

SELECT '2876','',''

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gcook 48147 SSC Veteran Points: 222 More actions · Answer 2

Hi Lowell. The question has been updated. It posted before I finished editing.

Thanks,

-Gary

Michael L John One Orange Chip Points: 27056 More actions · Answer 3

Well, take a look at your SELECT query, and then look at your UPDATE query.

In the select, your WHERE clause is testing for the value 'Suite' in Line2.

In the update, your WHERE clause is limiting the rows to update to a single row with the id = 1234

That's why you are only getting one row.

How about:

UPDATE address

SET Line1 = CONCAT (Line1,', ',Line2)

FROM address

WHERE Line2 LIKE 'Suite%

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Lowell SSC Guru Points: 323518 More actions · Answer 4

you probably want to cleanup line2 as well;

i think it's something like this:

CREATE TABLE #address(AddressID int, Line1 varchar(100), Line2 varchar(100) )

INSERT INTO #address

SELECT '1234','123 Main St.','Suite 2000' UNION ALL

SELECT '6596','2400 Peach Tree Lane','Suite 43' UNION ALL

SELECT '2876','12 Silver Rd','Apartment 23'

UPDATE #address

SET Line1 = CONCAT (Line1, ', ',Line2),

Line2 = ''

--highlight the selec tbelow to visualize the exact updates:

--SELECT Line1 ,Line2, CONCAT (Line1, ', ',Line2),''

FROM [#address]

--inner join someOtherTable On....

WHERE Line2 LIKE 'Suite%'

SELECT * FROM #address

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gcook 48147 SSC Veteran Points: 222 More actions · Answer 5

Thank you all, that worked! I thought the SET required a subquery as that is how the examples were formatted that I found online. This is much simpler and more direct. Still learning, thanks!

This is what it ended up doing:

BEGIN TRAN AddressUpdate

GO

UPDATE address

SET Line1 = CONCAT (Line1,', ',Line2), Line2 = NULL

FROM address

WHERE Line2 LIKE 'Suite%'

Verify:

SELECT Line1 FROM address

WHERE AddressID = '1234'

Perfect!

COMMIT TRAN AddressUpdate

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

gcook 48147 (11/14/2014)
I thought the SET required a subquery as that is how the examples were formatted that I found online. This is much simpler and more direct. Still learning, thanks!

That's because SQL Server and it's Father, SyBase, both use T-SQL and is the only dialect of SQL that I personally know of that allows the use of a FROM clause in UPDATE, which works very much like a FROM clause in a SELECT. Of course, it has some of the same caveats so you need to pay attention to how you write the code but it's incredibly powerful and, as you noticed, is more simpler to write in most cases.

--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)