If you want to update values in one table with values from another table, you can use a subquery as an expression in the SET clause. The subquery should return only one row for each row in the update table that matches the WHERE clause. The tutorial exercise below shows you a good example:

-- Create another table

CREATE TABLE ggl_rates (id INTEGER,

comment VARCHAR(16))

Go

-- Insert some rows in the new table

INSERT INTO ggl_rates VALUES (101, 'The best')

Go

INSERT INTO ggl_rates VALUES (102, 'Well done')

GO

INSERT INTO ggl_rates VALUES (103, 'Thumbs up')

Go

-- Update ggl_links with values from ggl_rates

UPDATE ggl_links SET notes = (

SELECT comment FROM ggl_rates

WHERE ggl_rates.id = ggl_links.id

)

WHERE id > 0 AND id < 110

GO

(3 row(s) affected)

-- View the updated values

SELECT * FROM ggl_links

WHERE id > 0 AND id < 110

GO

id url notes counts created

101 www.rendc.org The best 999 2006-04-30

102 www.rendc.org/html Well done 0 2007-05-19

103 www.rendc.org/sql Thumbs up NULL 2007-05-19

Note that if column names are confusing between the inner table and the outer table, you need to pref